MySql UPDATE with SUM in same table

Question

I'm working with a table (results) that has the following structure (don't ask, I didn't build it)

id | record_type | user_id | answer_id | choice | score | total |    email
-------------------------------------------------------------------------------
1    email         xxxxxxx                                  0     userX@site.com
2    answer        xxxxxxx    aaaaaa       A       0
3    answer        xxxxxxx    bbbbbb       A       0
4    answer        xxxxxxx    cccccc       B       10
5    email         yyyyyyy                                  0     userY@site.com
6    answer        yyyyyyy    aaaaaa       A       0
7    answer        yyyyyyy    bbbbbb       A       0
8    answer        yyyyyyy    cccccc       A       0
9    email         zzzzzzz                                  0     userZ@site.com
10   answer        zzzzzzz    aaaaaa       A       0
11   answer        zzzzzzz    bbbbbb       A       0
12   answer        zzzzzzz    cccccc       B       10

It's a survey and the score values of correct answers changed after the surveys were submitted. I've already run an update to set the score values for "correct" answers to 10 and now I need to update the total for the rows with record_type: email so we can contact the winners.

The goal would be to set the total column for rows 1,5 and 9 to be 10,0 and 10

I'm thinking of something like this

UPDATE results SET total = SUM(score) 
FROM results GROUP BY user_id WHERE user_id = user_id

But that doesn't look right and I'm worried that I may be going down the wrong path.

ypercubeᵀᴹ · Accepted Answer · 2016-06-24T12:51:59.803

11

UPDATE 
        results AS r 
    JOIN
        ( SELECT   user_id, 
                   SUM(score) AS sum_score
          FROM     results 
          WHERE    record_type = 'answer'
          GROUP BY user_id
        ) AS grp
       ON  
           grp.user_id = r.user_id 
SET 
       r.total = grp.sum_score
WHERE 
       r.record_type = 'email';

Regarding efficiency, an index on (record_type, user_id, score) would help both to efficiently compute the derived table and with the self-join.

edited Jun 24 '16 at 12:51

answered Feb 21 '13 at 23:23

ypercubeᵀᴹ

113,259
19
174
235

Question for you guys...I'm running this on a table with ~400K rows and it's crashing (probably running out of memory). Any suggestions to resolve? Like, a way to run the query in chunks? – byron Feb 25 '13 at 18:55
This might look "complicated" but is actually easy to implement and works like a dream (you need a unique index, I had to create one for my requirement) – zzapper Jun 24 '16 at 12:26
lovely. Thank you very much – allkenang Feb 04 '18 at 11:27

score 3 · Answer 2 · answered Feb 21 '13 at 22:46

3

You are almost there.

UPDATE results r SET total = 
    (select SUM(score) FROM results r2 
     WHERE r2.user_id = r.user_id 
     and r2.record_type = 'answer')
where r.record_type = 'email';

This should work

answered Feb 21 '13 at 22:46

Achrome

7,773
14
36
45

1

It will not work on MySQL server. http://stackoverflow.com/questions/653826/update-with-sum-in-mysql – Kamil Feb 21 '13 at 23:05
1

It returns `Error Code: 1093. You can't specify target table 'r' for update in FROM clause` – Kamil Feb 21 '13 at 23:12
1

Wow, worked for my case. Those who said it's not working, would you just alter it to fit your case please ! – Hamzeh Soboh Jun 02 '17 at 20:39
it works because of using `results r2` in the sub query – Ahmed Taha Feb 26 '19 at 15:46

MySql UPDATE with SUM in same table

2 Answers2