mysql recount column in mysql based on rows in secondary table

Question

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)

mysql recount column in mysql based on rows in secondary table

Sign up