用平均列更新记录 [英] Updating a record with an average of a column
本文介绍了用平均列更新记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在寻找status = 0的一列平均值,并将其更新到该表的另一条记录中. 这是我正在尝试使用的查询.
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?
谢谢!
推荐答案
您可以将表与子查询连接,该子查询分别计算每个用户名的平均值,
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'
更新1
您似乎想计算所有具有status = 0
的记录的总平均值,其结果将在Average
的记录上更新
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演示
- SQLFiddle Demo
这篇关于用平均列更新记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文