I am doing a special migration from someone's custom CMS to a WP installation. I successfully got the posts and comments migrated into their respective tables. Now I need to get the comment count into the wp_posts table for each matching id. I have something like this. I'm close, I think:

SELECT COUNT(comment_content) AS total_comments, comment_post_ID AS commentID
FROM wp_comments
GROUP BY comment_post_ID;
--mysql won't let me run the group by on just the returned count column, so I'm stuck with    two columns of data when I just want to insert one

UPDATE wp_posts SET comment_count = total_comments WHERE wp_posts.ID = commentID;
--So I tried to set my returned data to variables and assign them into the update statement..which doesn't work

Anyway, perhaps I need to iterate all the posts, find a matching ID and update it by moving the cursor? Or is there an easier way i'm missing somehow?


The easy way is to just write PHP to go through all the posts and call wp_update_comment_count() on each post ID.

  • But let's say I really wanted to figure this out with MySQL. Any idea how? – Lynn Nov 30 '12 at 22:11

And I finally figured it out. For those who want to know how something like this is handled with MySQL, here it is:

    UPDATE wp_posts, (SELECT COUNT(comment_content) AS total_comments, comment_post_ID
FROM wp_comments
GROUP BY comment_post_ID) x
       SET wp_posts.comment_count = x.total_comments
       WHERE wp_posts.ID = x.comment_post_ID;
  • 1
    That doesn't account for deleted or spam comments. You need to only count comments with comment_approved = '1'. – Otto Dec 9 '12 at 19:47
  • Good point. Though I was doing this from a custom built CMS. So the comments either existed or didn't. No spam or deleted status. – Lynn Dec 11 '12 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.