mysql recount column in mysql based on rows in secondary table

Question

Hi, I took over a database with two tables, lets name them entries and comments. The entries table contains a column named comment_count which holds the amount of rows with entry_id in comments corresponding to that row in entries.

Lately this connection has become terribly out of sync due to version switching of the codebase. I need help to build a query to run in phpmyadmin to sync these numbers again. The amount of rows in entries is around 8000 and the rows in comments is around 80000 so it shouldn't be any problems to run the sync-query.

Structure:

• entries countains:
• id | comment_count | etc
• comments contains
• id | blogentry_id | etc

The only way I can think of is to loop each entry in the entries table with php and update individually but that seems extremly fragile compared to a pure SQL solution.

I'd appriciate for any help!

Answer 1

I think a pure SQL solution would invlolve using a subquery to gather the counts from the comments table having the entries table as the driver. Something like the following should "loop" over the entries table and for each row perform the subquery (that may be the incorrect terminology) and update the comment count to be that of the corresponding counts off of the auxillary table. Hope that helps!

UPDATE entries ent SET comment_count = (SELECT COUNT ( * ) FROM comments cmt WHERE cmt.blogentry_id = ent.id)

Answer 2

INSERT
INTO    entries (id, comment_count)
SELECT  blogentry_id, COUNT(*) AS cnt
FROM    comments
GROUP BY
        blogentry_id
ON DUPLICATE KEY
UPDATE  comment_count = cnt