Hi

I am trying to write a query that takes the count() from one table and updates a second table with the value.

To be more specific, I have two tables:

wp_posts
wp_comments

(that's right, I'm messing with Wordpress)

I want to get the number of comments for each blog post and update the comment_count column in the wp_posts table with it.

This is what I have so far:

UPDATE wp_posts SET wp_posts.comment_count = SELECT count(comment_ID) as comment_count FROM wp_comments where wp_comments.post_ID = wp_posts.ID

What am I doing wrong?

vs

    Worked it out!

    update wp_posts p JOIN wp_comments c ON p.ID = c.comment_post_ID
    SET p.comment_count = (SELECT count(comment_ID) as comment_count
    FROM wp_comments c where c.comment_post_ID = p.ID )

      Write a Reply...