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!
UPDATE playercareer c
ReplyDeleteINNER JOIN (
SELECT gameplayer, SUM(points) as total
FROM games
GROUP BY gameplayer
) x ON c.playercareername = x.gameplayer
SET c.playercareerpoints = x.total