Update a column with a COUNT of other fields is SQL?

I have the following tables set up:

Articles:
ID | TITLE | CONTENT | USER | NUM_COMMENTS

COMMENTS
ID | ARTICLE_ID | TEXT

I need a sql statement which updates the NUM_Comments field of the articles table with teh count of the comments made against the article like:

update articles a, comments f 
set a.num_comments =  COUNT(f.`id`)
where f.article_id = a.id

The sql above 开发者_开发问答doesn't work and I get an Invalid Use fo Group function error. I'm using MySQL Here.

You can't have a join in an update statement. It should be

update articles
set num_comments =
(select count (*) from comments
where comments.article_id = articles.id)

This will update the entire articles table, which may not be what you want. If you intend to update only one article then add a 'where' clause after the subquery.

This should work.

UPDATE articles a SET num_comments = 
(SELECT COUNT(*) FROM comments c WHERE c.article_id = a.id)

But i would rather update only one record when comment has been posted:

UPDATE articles a SET num_comments = 
(SELECT COUNT(*) FROM comments c WHERE c.article_id = 100) WHERE a.id = 100

count (*) might have some problems, especially with blank space between count and (*) ...

so working sql on sqlite, pgsql would be:

update articles 
  set num_comments = 
    (select count(id) from comments 
     where comments.article_id = articles.id)

To update based on a column count alone, you could do something like:

update articles,
 (select count (*) 
  from comments
  where comments.article_id = articles.id) as newtotals
set articles.num_comments = newtotals.count;

or ... if you had a situation that required rolling counts:

update articles,
 (select (count (*)) + (articles.num_comments) as count 
  from comments
  join articles on 
    comments.article_id = articles.id
  group by articles.id) as newtotals
set articles.num_comments = newtotals.count;

you cant do it in a generic inner join way. but you can do it in another way by:

1- Select all the ids from the articles table

2- iterate them and execute the following command

update articles set NUM_COMMENTS = (select count(id) from comments where id = $id) where id = $id

to enhance it more, in the 1st select dont select all the values especially when that table is too large, you need to iterate the articles and get 1000 records per iteration. This way u will maintain a healthy DB threads from your DB pool and you also save bandwidth.