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