使用另一个表中的 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屋！