带有 IN 和子查询的 MYSQL UPDATE
您好，我有这样的表格:
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))
相关文章