MYSQL UPDATE with IN and subquery

Question

Hi, I have tables like this:

entry in the table:

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

comments in the table:

id | eid | a comment
_____________________
1 | 1 | comment sdfd
2 | 1 | testing testing
3 | 1 | comment text
4 | 2 | fictitious comment
5 | 2 | sample comment
6 | 1 | fg fgh dfh

Request 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:

entry in the table:

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

Expected results:

entry in the table:

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

Any help would be appreciated.

Answer 1

Using:

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

Answer 2

If you really need total_comments in a separate table, I would do it VIEW.

 CREATE VIEW entry AS SELECT id, COUNT(comments) AS total_comment FROM comments GROUP BY id 

Thus, you avoid the task of servicing the updating of the total_comments common table.

Answer 3

What I expect. The identifier is in the set that you give it, so total_comments = total_comments + 1.

It will not add one for each instance of the same value: this is not how IN works. IN will return a simple boolean yes / no.

Answer 4

Try:

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

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