使用IN和子查询进行MYSQL更新

Hi i have tables like this :

嗨我有这样的表:

table entry :

表格条目:

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

id | 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

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

id | 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

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

Any help will be appreciated.

任何帮助将不胜感激。

5 个解决方案

#1

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))

#2

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表的维护任务。

#3

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将返回一个简单的布尔值yes / no。

#4

Try:

UPDATE entry
  SET total_comments = (SELECT COUNT(*) 
                        FROM comments
                        WHERE entry.id = comments.eid
                        GROUP BY id)

#5

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))