mysql带有in的子查询_带IN和子查询的MYSQL UPDATE

Hi i have tables like this :

table entry :

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

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

Expected results :

table entry :

id | total_comments

_____________________

1 | 4

2 | 2

3 | 0

4 | 0

Any help will be appreciated.

解决方案

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