Search code examples
mysqlsqlmysql-error-1111

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.

Solution

  • 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.