MYSQL UPDATE with IN and Subquery

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

---

If you really need total_comments in a separate table, I would make that a 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.

---

That's exactly what I'd expect. The id is IN the set you give it, so 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.

---

Try:

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

---

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