Geek asks and answers

MySql UPDATE with SUM in one table

I work with a table (results) that has the following structure (do not ask, I did not create 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

The results of the survey and the assessment of the correct answers changed after the polls were submitted. I already started the update to set the score for the “correct” answers to 10, and now I need to update the total number of lines using record_type: email so that we can contact the winners.

The goal would be to set the common column for rows 1.5 and 9 to 10.0 and 10

I think of something like this

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

But this does not look right, and I'm worried that maybe I'm wrong.

+5
source share
3 answers
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';

, (record_type, user_id, score) , .

+8

.

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';

+3

Hi, I followed the ur update instructions, but an error occurred Missing set key word.

Below is the update instruction.

UPDATE IFRS_SIT_USER.t_318038 AS r 
    JOIN
        ( SELECT   business_unit, Report_no, Y_Ordinate,
                          SUM(Calc_Permata_amt) AS sum_calc_amt
          FROM  IFRS_SIT_USER.t_318038  
          WHERE    business_unit = 'DA-01-PERMATA' and Report_no='F 12.00' and asof_date='31-Mar-2017'
          GROUP BY Y_Ordinate, business_unit, Report_no
) AS grp
       ON  
           grp.Y_Ordinate= r.Y_Ordinate  
SET  
        r.Fnl_Calc_Permta_amt=grp.sum_calc_amt
WHERE 
       r.business_unit = 'DA-01-PERMATA' and r.Report_no='F 12.00' and r.asof_date='31-Mar-2017'
-2
source

More articles:


All Articles