在同一张表中使用SUM进行MySql更新

Question

我正在处理一个表格（results），其结构如下（别问我，这不是我建的）

id | record_type | user_id | answer_id | choice | score | total |    email
-------------------------------------------------------------------------------
1    email         xxxxxxx                                  0     [e-mail]
2    answer        xxxxxxx    aaaaaa       A       0
3    answer        xxxxxxx    bbbbbb       A       0
4    answer        xxxxxxx    cccccc       B       10
5    email         yyyyyyy                                  0     [e-mail]
6    answer        yyyyyyy    aaaaaa       A       0
7    answer        yyyyyyy    bbbbbb       A       0
8    answer        yyyyyyy    cccccc       A       0
9    email         zzzzzzz                                  0     [e-mail]
10   answer        zzzzzzz    aaaaaa       A       0
11   answer        zzzzzzz    bbbbbb       A       0
12   answer        zzzzzzz    cccccc       B       10

这是一项调查，提交后正确答案的分值发生了变化。我已经运行了更新操作，将“正确”答案的分值设置为10，现在我们需要更新记录类型为“电子邮件”的行的总分，以便联系获胜者。

目标是将第1、5和9行的总列设置为10、0和10。

我考虑使用以下方法：

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

但是这看起来不对，我担心自己可能走错了方向。

- byron

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

关于效率，对于 (record_type, user_id, score) 建立索引既能有效地计算派生表，又能提高自连接的效率。

- ypercubeᵀᴹ

---

你们好，我有一个问题... 我正在运行一个表格，大约有40万行数据，但它崩溃了（可能是内存耗尽了）。 有什么建议可以解决吗？ 比如说，有没有一种分批次运行查询的方法？ - byron

这可能看起来很“复杂”，但实际上很容易实现，并且像梦想一样工作（您需要一个唯一的索引，我不得不为我的需求创建一个）。 - zzapper

可爱的。非常感谢。 - allkenang

Answer 2

您已经接近成功了。

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

这应该可以正常工作

- Achrome

---

它在MySQL服务器上无法工作。https://dev59.com/YUbRa4cB1Zd3GeqP4uiY - Kamil

它返回“错误代码：1093。您不能在FROM子句中更新目标表'r'”。 - Kamil

哇，对我的情况起作用了。那些说它不起作用的人，请您修改一下以适应您的情况！ - Hamzeh Soboh

它之所以能够工作，是因为在子查询中使用了 results r2。 - Ahmed Taha