There was a user in the #wordpress IRC channel who was having some problems with his database’s
comment_count field being incorrectly updated when deleting comments.
Since there were several of us who weren’t sure if their comment_counts had been properly updated when the field was added, I whipped up a series of SQL queries. There was a good bit of playing around, but the final version is:
SELECT id, comment_count FROM wp_posts p WHERE p.comment_count >0 AND ( id, comment_count ) NOT IN ( SELECT comment_post_id, COUNT( * ) FROM wp_comments c WHERE comment_approved = '1' GROUP BY comment_post_id )
Of course update
wp_ with whatever your database prefix is, assuming you didn’t use the default.
Updating these values should be about as simple, although I have not tested this:
UPDATE wp_posts p SET p.comment_count = ( SELECT COUNT(*) FROM wp_comments WHERE comment_post_id = p.id AND comment_approved = '1' )