MYSQL UPDATE with IN and Subquery

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams 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 8, 2010 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 8, 2010 at 14:30

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.
                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 8, 2010 at 14:39
                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 8, 2010 at 14:37
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))
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.