带有 IN 和子查询的 MYSQL UPDATE [英] MYSQL UPDATE with IN and Subquery

问题描述

您好，我有这样的表格:

Hi i have tables like this :

表格条目:

标识 |总评论
_____________________
1 |0
2 |0
3 |0
4 |0

id | total_comments
_____________________
1 | 0
2 | 0
3 | 0
4 | 0

表格注释:

标识 |开斋节 |评论
_____________________
1 |1 |评论 sdfd
2 |1 |测试测试
3 |1 |评论文字
4 |2 |虚拟评论
5 |2 |示例评论
6 |1 |fg fgh dfh

id | eid | comment
_____________________
1 | 1 | comment sdfd
2 | 1 | testing testing
3 | 1 | comment text
4 | 2 | dummy comment
5 | 2 | sample comment
6 | 1 | fg fgh dfh

我写的查询:

UPDATE entry 
   SET total_comments = total_comments + 1 
 WHERE id IN ( SELECT eid 
                 FROM comments 
                WHERE id IN (1,2,3,4,5,6))

我得到的结果是:

表格条目:

标识 |总评论
_____________________
1 |1
2 |1
3 |0
4 |0

id | total_comments
_____________________
1 | 1
2 | 1
3 | 0
4 | 0

预期结果:

表格条目:

标识 |总评论
_____________________
1 |4
2 |2
3 |0
4 |0

id | total_comments
_____________________
1 | 4
2 | 2
3 | 0
4 | 0

任何帮助将不胜感激.

推荐答案

使用:

UPDATE entry 
   SET total_comments = (SELECT COUNT(*)
                           FROM COMMENTS c
                          WHERE c.eid = id
                       GROUP BY c.eid)
 WHERE id IN ( SELECT eid 
                 FROM comments 
                WHERE id IN (1,2,3,4,5,6))

这篇关于带有 IN 和子查询的 MYSQL UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！