用另一个表中的SUM更新表 [英] Update table with SUM from another table

问题描述

我正在尝试将一个表的总和与另一个表的总和进行一次简单的更新，但是由于某种原因，它仅更新了一行.以下是表格中的相关信息:

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:

游戏

gameplayer|points
----------------
John      |5
Jim       |3
John      |3
Jim       |4

玩家职业

playercareername|playercareerpoints
-----------------------------------
John            |0
Jim             |0

现在，最终，我希望运行更新后最后一张表看起来像这样:

Now ultimately, I would like the last table to look like this after running the update:

玩家职业

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!

推荐答案

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

这篇关于用另一个表中的SUM更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！