带有 IN 和子查询的 MYSQL UPDATE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2993110/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL UPDATE with IN and Subquery
提问by Johal
Hi i have tables like this :
嗨，我有这样的表：
table entry :
表条目：
id | total_comments
_____________________
1 | 0
2 | 0
3 | 0
4 | 0
身 | total_comments
_____________________
1 | 0
2 | 0
3 | 0
4 | 0
table comments :
表评论：
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
身 | 开斋节 | 评论
_____________________
1 | 1 | 评论 sdfd
2 | 1 | 测试测试
3 | 1 | 评论文本
4 | 2 | 虚拟评论
5 | 2 | 示例评论
6 | 1 | fg fgh dfh
Query i write :
我写的查询：
UPDATE entry
SET total_comments = total_comments + 1
WHERE id IN ( SELECT eid
FROM comments
WHERE id IN (1,2,3,4,5,6))
Results i get is :
我得到的结果是：
table entry :
表条目：
id | total_comments
_____________________
1 | 1
2 | 1
3 | 0
4 | 0
身 | total_comments
_____________________
1 | 1
2 | 1
3 | 0
4 | 0
Expected results :
预期成绩 ：
table entry :
表条目：
id | total_comments
_____________________
1 | 4
2 | 2
3 | 0
4 | 0
身 | total_comments
_____________________
1 | 4
2 | 2
3 | 0
4 | 0
Any help will be appreciated.
任何帮助将不胜感激。
回答by OMG Ponies
Use:
用：
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))
回答by mluebke
If you really need total_comments in a separate table, I would make that a VIEW.
如果您真的需要单独的表中的 total_comments，我会将其设为 VIEW。
CREATE VIEW entry AS
SELECT id, COUNT(comments) AS total_comment
FROM comments
GROUP BY id
This way you avoid the maintenance task of updating the total_comments table altogether.
通过这种方式，您可以完全避免更新 total_comments 表的维护任务。
回答by Satanicpuppy
That's exactly what I'd expect. The id is IN the set you give it, so total_comments = total_comments + 1.
这正是我所期望的。id 在你给它的集合中，所以 total_comments = total_comments + 1。
It's not going to add one for each instance of the same value: that's not how IN works. IN will return a simple boolean yes/no.
它不会为相同值的每个实例添加一个：这不是 IN 的工作方式。IN 将返回一个简单的布尔值是/否。
回答by VeeArr
Try:
尝试：
UPDATE entry
SET total_comments = (SELECT COUNT(*)
FROM comments
WHERE entry.id = comments.eid
GROUP BY id)
回答by Johal
UPDATE entry e
SET total_comments = ( SELECT COUNT(*) FROM comments WHERE eid = e.id)
WHERE
e.id in (SELECT eid FROM comments WHERE id IN (1,2,3,4,5,6))
