MySQL 使用另一个表中的 SUM 更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10091336/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update table with SUM from another table
提问by BigJay
I am trying to what I thought was going to be a simple update of a table with the sum from another table, but for some reason, it is only updating one row. Here is what the relevant info from the tables look like:
我正在尝试使用另一个表的总和对一个表进行简单的更新，但由于某种原因，它只更新了一行。以下是表格中的相关信息：
games
游戏
gameplayer|points
----------------
John |5
Jim |3
John |3
Jim |4
playercareer
球员生涯
playercareername|playercareerpoints
-----------------------------------
John |0
Jim |0
Now ultimately, I would like the last table to look like this after running the update:
现在最终，我希望最后一个表在运行更新后看起来像这样：
playercareer
球员生涯
playercareername|playercareerpoints
-----------------------------------
John |8
Jim |7
This is the query I attempted that only updates the first row:
这是我尝试的仅更新第一行的查询：
UPDATE playercareer
SET playercareer.playercareerpoints =
(
SELECT
SUM(games.points)
FROM games
WHERE
playercareer.playercareername=games.gameplayer
)
I can't seem to find the answer to this. Thanks in advance for your time and advice!
我似乎无法找到这个问题的答案。提前感谢您的时间和建议！
回答by bobwienholt
UPDATE playercareer c
INNER JOIN (
SELECT gameplayer, SUM(points) as total
FROM games
GROUP BY gameplayer
) x ON c.playercareername = x.gameplayer
SET c.playercareerpoints = x.total
