17

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!

Francis P
  • 13,377
  • 3
  • 27
  • 51
BigJay
  • 171
  • 1
  • 1
  • 3
  • Sorry, I am using MySQL 5.5.16. – BigJay Apr 10 '12 at 15:17
  • 4
    I tested your code with SQL Server and it works fine (http://sqlfiddle.com/#!3/97125/2). Maybe you should verify your player names (maybe one has a white space and the match cannot be made). Ultimately, I'd recommand you use IDs instead of Names and use a relational table. – Francis P Apr 10 '12 at 15:18
  • 2
    Oh well, still works fine with MySQL 5.5! (http://sqlfiddle.com/#!2/97125/1) – Francis P Apr 10 '12 at 15:19
  • This is odd. @FrancisP's sqlfiddle looks perfect. @BigJay, maybe the issue is related to the way your tables are structured? Can you post the output of `SHOW CREATE TABLE games` and `SHOW CREATE TABLE playercareer`? – Matthemattics Apr 10 '12 at 15:30
  • 1
    This isn't a particularly good idea. You're adding redundancy to your database by adding data which it already contains. Is there a reason you can't select the SUM when you need it? – Jim Apr 10 '12 at 15:31
  • 3
    WOW! Just discovered sqlfiddle here in the comments... fantastic tool! – Leniel Maccaferri Apr 10 '12 at 15:39
  • I just tried the suggestion made by Francis and added a new column to each table called playerid and playercareerid. Populated those fields so the ID's match the playernames, updated my query, re-ran it and it worked like it should have. So Francis was probably right in the fact there may be a match error somewhere. @Jim This is a very casual database with at max 7 users with an update frequency of about a month apart. This data is relayed to a PHP page. I am certain there are more appropriate approaches, but I am still quite a beginner and hopefully learn more as I go. Thanks Francis! – BigJay Apr 10 '12 at 15:47
  • this is great but what if we have million rows on both the tables. Which fields should we consider for indexing ? – hsuk Mar 01 '18 at 17:08

2 Answers2

48
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
bobwienholt
  • 17,420
  • 3
  • 40
  • 48
  • 1
    hey @bobwienholt this is great but what if we have million rows on both the tables. Which fields should we consider for indexing ? – hsuk Mar 01 '18 at 17:08
  • It does not matter how many rows you have; the most helpful thing you can do, if possible, is to index the field(s) you are using to join your tables. – colonelclick Nov 22 '19 at 01:56
0

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;
Rahul Biswas
  • 3,207
  • 2
  • 10
  • 20
DSknaCoder
  • 1
  • 1