byron byron - 6 months ago 41
SQL Question

MySql UPDATE with SUM in same table

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

        results AS r 
        ( SELECT   user_id, 
                   SUM(score) AS sum_score
          FROM     results 
          WHERE    record_type = 'answer'
          GROUP BY user_id
        ) AS grp
           grp.user_id = r.user_id 
SET = grp.sum_score
       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.