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

Question

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.

Why exactly do you want to store that information in your articles table? Have you considered counting the comments each time you need that information? That way you avoid having duplicate informations in your database schema. — plang, May 26 '11 at 07:42
Well the articles table is huge and I want to avoid having to do a join because I also need to sort articles based upon how most commented. — Ali, May 26 '11 at 07:44
Ok, then another option for you is some kind of "materialized view". — plang, May 26 '11 at 07:48
This seems to be a duplicate of http://stackoverflow.com/questions/1216175/mysql-count-records-from-one-table-and-then-update-another — No'am Newman, May 26 '11 at 09:41

score 49 · Accepted Answer · answered May 26 '11 at 07:43

49

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.

answered May 26 '11 at 07:43

No'am Newman

6,395
5
38
50

1

Just note that you can join in an update if you alias the table to be updated - see http://stackoverflow.com/questions/1293330/sql-update-with-join – StuartLC May 26 '11 at 07:59
What's the way to 'make' MySQL do this on every comment insert (just not recount everything, only increment). I'm thinking about incrementation of num_comments value with each insert using an additional query, but that seems dull to me. Can MySQL do that on its own? – Luka Apr 05 '18 at 15:32
It can be done automatically by a post-insert trigger on the comments table. But then one has also to write a post-delete trigger, in case a comment is deleted. There is a good reason why one is not supposed to store counts! – No'am Newman Apr 05 '18 at 16:17
"You can't have a join in an update statement" - It is not true! You indeed *should* use a join when your update operation need it. – sdlins Jun 24 '20 at 15:10
@sdlins: It depends on your database system. – No'am Newman Jun 25 '20 at 09:30
@No'amNewman yeah! It really depends. My bad, I had not seen it was mysql since I came here after browser in other dbms questions. Sorry! – sdlins Jun 25 '20 at 15:05

score 10 · Answer 2 · answered May 26 '11 at 07:43

10

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

answered May 26 '11 at 07:43

Deniss Kozlovs

4,761
2
28
35

I would be running this update query once every few hours via a cron job though – Ali May 26 '11 at 07:59

score 0 · Answer 3 · edited Jun 12 '19 at 08:20

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;

score 0 · Answer 4 · answered Nov 15 '18 at 23:35

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)

score -2 · Answer 5 · answered May 26 '11 at 07:51

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.

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

5 Answers5

Linked