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     [email protected]
2    answer        xxxxxxx    aaaaaa       A       0
3    answer        xxxxxxx    bbbbbb       A       0
4    answer        xxxxxxx    cccccc       B       10
5    email         yyyyyyy                                  0     [email protected]
6    answer        yyyyyyy    aaaaaa       A       0
7    answer        yyyyyyy    bbbbbb       A       0
8    answer        yyyyyyy    cccccc       A       0
9    email         zzzzzzz                                  0     [email protected]
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.

Answer 1

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.

Answer 2

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