0

What I want to do must be broadly applicable, but it's hard to describe, so I'll give a dummy version of my circumstances (pretend posts are sometimes co-authored):

Table A: Users

id  |  name  |...demographics....|  post_count | draft_count

and

Table B: Posts

id  |  title  |...bunch of columns...| is_draft

whilst

Table C: Users_Posts

id  | user_id  | post_id

Now let's pretend the post_count and draft_count columns were just added—there are thousands of posts and users. I want to populate those columns! I can get this far:

SELECT count(*) FROM `people` 
    JOIN `users_posts` ON `users`.`id`=`users_posts`.`users_id`
    JOIN `posts` ON `users_posts`.`post_id`=`posts`.`id`
    WHERE `posts`.`is_draft` = 1 AND `users`.`id`= N

Which gives me the number I wish to update user N 's draft_count field. But I'm not sure how to take this whole thing as a clause in an UPDATE statement. I've read how to iterate in MySQL and I think I get it, so if I can get this to work I think I can do it for each user. Moreover, though I could easily achieve this in PHP, I will need to periodically run this because there are probably only going to be batch additions to these tables (the IRL application, obviously, is not a blog). Again, I've read up on how to create a custom MySQL function, and I think I can do that if I can just get this bloody update business to work haha. Help appreciated!

Jonline
  • 1,677
  • 2
  • 22
  • 53

1 Answers1

1
UPDATE users
JOIN (
    SELECT COUNT(posts.id) as draft_count, users_posts.users_id
    FROM posts 
    JOIN users_posts ON users_posts.post_id = posts.id
    WHERE posts.is_draft = 1
    GROUP BY users_posts.users_id
) as draft_counts ON users.id = draft_counts.users_id
SET users.draft_count = draft_counts.draft_count

It might be faster with a temp table if you have a lot of rows.

Vatev
  • 7,493
  • 1
  • 32
  • 39