MYSQL UPDATE with IN and Subquery

Question

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.

score 22 · Accepted Answer · answered Jun 07 '10 at 21:04

22

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

answered Jun 07 '10 at 21:04

OMG Ponies

325,700
82
523
502

yeah i am looking for something like that... my motive is to run update query based on subquery.... its to update "total_comments" in "entry" table with acutal number of comments, for id in "entry" if i run query that i have specified it only adds 1 for all number of comments found in subqery . Here what sql query turns into after subquery runs: UPDATE entry SET total_comments = total_comments + 1 WHERE id IN (1,1,1,2,2,1) so update runs only once for 1 and 2 id's in entry table. Coz its how IN works. But i want some how to run it 4 times for 1 and 2 times for 2. – Johal Jun 08 '10 at 14:28
The only way i think is to to run subquery something like that what i dont know how to do: UPDATE entry SET total_comments = total_comments + @count WHERE id IN ( SELECT eid, count(*) as @count FROM comments WHERE id IN (1,2,3,4,5,6)) but then In doesnt work coz of multiple rows and also i dont know how to refer @count in set condition :( – Johal Jun 08 '10 at 14:30

score 3 · Answer 2 · edited Dec 14 '15 at 14:50

3

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.

edited Dec 14 '15 at 14:50

robsch

9,358
9
63
104

answered Jun 07 '10 at 21:03

mluebke

8,588
7
35
31

+1: I completely agree, this is a better design choice than the current table structure. – OMG Ponies Jun 07 '10 at 21:14

score 1 · Answer 3 · answered Jun 07 '10 at 21:02

1

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.

answered Jun 07 '10 at 21:02

Satanicpuppy

1,569
8
11

i know... but i want some method so that i can count number of entry.id in subqery. I can do it by running subquery sperate and then running update query using for loop for all those. But i wanna do it in one query thats my porblem :( – Johal Jun 08 '10 at 14:39

score 1 · Answer 4 · answered Jun 07 '10 at 21:03

1

Try:

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

answered Jun 07 '10 at 21:03

VeeArr

6,039
3
24
45

this will run for all records in entry table but i want to run for only specified records thats why i used IN clause... – Johal Jun 08 '10 at 14:37

score 0 · Answer 5 · answered Jun 08 '10 at 15:00

0

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

answered Jun 08 '10 at 15:00

Johal

597
1
7
23

MYSQL UPDATE with IN and Subquery

5 Answers5

Linked