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'
)
Babble Blabber
RSS