Updating a record with an average of a column

问题:

I'm looking to find the average of a column where status=0 and update it onto another record into that table. This is the query i'm attempting to use.

UPDATE mc25778 set balance=(AVG(balance WHERE status=0)) WHERE username="Average"

I get this error when trying to perform this task:

   Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE status=0)) WHERE username="Average"' at line 1

Any ideas how I can sort this out?

Thanks!

回答1:

you can join the table with a subquery which separately calculate the average for every username,

UPDATE  mc25778 a
        INNER JOIN 
        (
            SELECT  username, AVG(balance) avg_bal
            FROM    mc25778 
            WHERE   status = 0
            GROUP   BY username
        ) b ON a.username = b.username
SET     a.balance = b.avg_bal
WHERE   a.username = 'Average'

UPDATE 1

It looks like you want to calculate the total average for all records having status = 0 and the result of it will be updated on the record of Average

UPDATE  mc25778 a
        CROSS JOIN
        (
            SELECT  AVG(balance) avg_bal 
            FROM    mc25778 
            WHERE   status = 0
        )  b
SET     a.balance = b.avg_bal
WHERE   a.username = 'Average'

• SQLFiddle Demo

回答2:

Do this:

Declare @avg decimal(10,2)
Set @avg=(SELECT AVG(balance) FROM mc25778 WHERE status = 0)
UPDATE mc25778 set balance=@avg WHERE username="Average"