moodh moodh - 6 months ago 10
SQL Question

mysql recount column in mysql based on rows in secondary table

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

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