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

问题描述

我有这样的表:

表条目:

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

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

表注释:

id |开斋节评论
_____________________
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))

我得到的结果是:

表条目:

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

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

预期结果:

表条目:

id | total_comments
_____________________
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屋！