Update a column with a COUNT of other fields is SQL?
Hi guys 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
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;
上一篇: 快速更新SQL Server表中的所有行
下一篇: 用其他字段的COUNT更新列是SQL?