CODEWITHSUNDEEP
mysqlsum
BigJay
BigJay

Reputation: 181

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!

Upvotes: 18

Views: 32278

Answers (2)

DSknaCoder
DSknaCoder

Reputation: 1

Not same table names but this worked for me

update e
set e.Absence_Hours = a.hours
from [Thomas_Test].[dbo].Employee e
inner join (select empnum
                 , sum(hours) as hours
            FROM [Thomas_Test].[dbo].[Employee_Absence_Report]
            group by empnum) a 
        on a.empnum = e.emp_no;

Upvotes: 0

bobwienholt
bobwienholt

Reputation: 17610

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

Upvotes: 49

Related Questions