MySQL update table with values from same table

Question

I have a 4 column table, the primary key is a composite of id, gameId and flag.

id | gameId | score | flag
--------------------------
1  | 1      | 10    | 1
1  | 1      | 20    | 0
2  | 1      | 1     | 0
1  | 2      | 10    | 1
3  | 2      | 1     | 0

I need to update the table so that:

All of gameId 2 scores are added to gameId 1 where the id and flag is the same. (e.g. row 1 would have a score of 20 by adding the scores of row 1 and row 4)
If the above happens the gameId 2 row needs to be deleted.
Where the above isn't found (e.g. there is a row where the gameId is 2 but the id and flag don't match another row), the gameId can just be changed to 1.

So my table after the SQL is complete should look like this:

id | gameId | score | flag
--------------------------
1  | 1      | 20    | 1
1  | 1      | 20    | 0
2  | 1      | 1     | 0
3  | 1      | 1     | 0

How can I write this in SQL? Thanks :)

Yep, because the primary key is built from the id, gameId and flag, it doesn't matter if the id field is replicated. — David Normington
– David Normington, Commented Jul 11, 2012 at 16:21

Puggan Se · Accepted Answer · 2012-07-11 16:37:49Z

6

think this going to work:

Try 1

UPDATE score_list AS t2
LEFT JOIN score_list AS t1 ON
  (t1.id = t2.id AND t1.flag = t2.flag AND t1.gameId = 1)
SET
  t1.score = t1.score + t2.score,
  t2.gameId = IF(t1.gameId IS NULL, 1, t2.gameId)
WHERE t2.gameId = 2;

DELETE FROM score_list WHERE gameId = 2;

Try 2

# add scores to gameId = 1
UPDATE score_list AS t2
LEFT JOIN score_list AS t1 ON
  (t1.id = t2.id AND t1.flag = t2.flag AND t1.gameId = 1)
SET
  t1.score = t1.score + t2.score
WHERE t2.gameId = 2;

# try to move gameID 2 to gameId 1, using ignore to allow rows to fail, as gameId alredy exists
UPDATE IGNORE score_list SET gameId = 1 WHERE gameId = 2;

# delete all not updated rows from last query
DELETE FROM score_list WHERE gameId = 2;

edited Jul 11, 2012 at 16:37

answered Jul 11, 2012 at 16:24

Puggan Se

5,8462 gold badges25 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Normington Over a year ago

Thanks for the quick response! However I get this error: Mysqli statement execute error : Primary key/partition key update is not allowed since the table is updated both as 't2' and 't1'

dafero Over a year ago

Your answer inspired the solution of a related issue I was having. Thanks!

Collectives™ on Stack Overflow

MySQL update table with values from same table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related