Tuesday, April 10, 2012

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:



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!





1 comment:

  1. 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

    ReplyDelete