How to update wp_posts with just the returned comment count from wp_comments (SQL GROUP BY)

Question

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?

Answer 1

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

Answer 2

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;