How to add the number of rows of the first table to another with the condition
up vote
0
down vote
favorite
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
mysql sql
add a comment |
up vote
0
down vote
favorite
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
mysql sql
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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
mysql sql
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
mysql sql
mysql sql
edited Nov 10 at 14:53
Gordon Linoff
742k32285390
742k32285390
asked Nov 10 at 14:45
noobsaibot
257
257
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
up vote
1
down vote
accepted
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.
add a comment |
up vote
0
down vote
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)
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
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.
add a comment |
up vote
1
down vote
accepted
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.
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
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.
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.
answered Nov 10 at 14:51
Gordon Linoff
742k32285390
742k32285390
add a comment |
add a comment |
up vote
0
down vote
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)
add a comment |
up vote
0
down vote
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)
add a comment |
up vote
0
down vote
up vote
0
down vote
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)
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)
edited Nov 10 at 14:57
answered Nov 10 at 14:51
Madhur Bhaiya
15.3k52136
15.3k52136
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53240063%2fhow-to-add-the-number-of-rows-of-the-first-table-to-another-with-the-condition%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown