Myujth

There are two tables:

####comments#### ####news######
#cid#news_id# #id##comm_num# 
#1##1# #1###2#
#2##1# #2###1#
#3##2# #3###3#

I try to count and put the number of comments from the table 'comments' into the table 'news', but I get the wrong result. Why?

UPDATE news JOIN comments ON news.id = comments.news_id SET 
news.comm_num = ( SELECT COUNT( * ) 
FROM comments WHERE comments.news_id > 123)
WHERE news.id > 123

'comments.news_id' = 'id' of commented news from table 'news'

I wrote a working solution for particular cases, but I can’t figure out how to make a request with a condition larger than.

UPDATE news a 
SET comm_num = (SELECT COUNT(*) 
 FROM comments c 
 WHERE c.news_id = 123) 
WHERE a.id = 123 

Simply use a correlated subquery:

UPDATE news n 
 SET comm_num = (SELECT COUNT(*) 
 FROM comments c 
 WHERE c.news_id = n.id
 ) ;

I am not sure what the condition WHERE news.id > 123 is for.

You can get the count of total comments on a news using a Derived Table. Join this back to the news table on the news_id and update the values accordingly.

We use LEFT JOIN to handle the case where there is no comments on a news. And, Coalesce() function is used to change null to 0 (in case of no comments).

UPDATE news a 
LEFT JOIN (SELECT news_id, COUNT(*) AS comm_num 
 FROM comments 
 GROUP BY news_id) b 
 ON b.news_id = a.id 
SET a.comm_num = COALESCE(b.comm_num,0)

If you want to update only those news where id is more than 123; you can add the conditions as follows:

UPDATE news a 
LEFT JOIN (SELECT news_id, COUNT(*) AS comm_num 
 FROM comments 
 WHERE news_id > 123 -- add condition to Derived Table
 GROUP BY news_id) b 
 ON b.news_id = a.id 
WHERE a.id > 123 -- add condition here also to avoid updating id <= 123
SET a.comm_num = COALESCE(b.comm_num,0)