Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Sebastian [EMAIL PROTECTED] wrote:
I have two tables: news and comments.
i want to count the number of comments for
--- Gleb Paharenko [EMAIL PROTECTED] wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Isn't the previous query going to update the
news.comments with the last
Hello.
No, it isn't. Here is the test case:
create table comments(id int auto_increment,newsid int,primary key(id));
create table news(id int,comments int);
insert into news(id) values('1'),(2),(3),(4),(5);
insert into comments(newsid) values(1),(1),(2),(4),(4),(4);
update news set
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Hi. i came up with a similar query last night, but i didnt use group by.
I have question, it it better to use
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005 10:53:55 AM:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Hi. i came up with a similar
[EMAIL PROTECTED] wrote:
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005 10:53:55 AM:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Hi. i came up with a similar
[EMAIL PROTECTED] wrote:
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005 10:53:55 AM:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Hi. i came up with a similar
Nuno Pereira wrote:
[EMAIL PROTECTED] wrote:
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005
10:53:55 AM:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Hi. i came up
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005 01:19:32 PM:
Nuno Pereira wrote:
[EMAIL PROTECTED] wrote:
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005
10:53:55 AM:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
Sebastian wrote:
Nuno Pereira wrote:
[EMAIL PROTECTED] wrote:
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005
10:53:55 AM:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Nuno Pereira [EMAIL PROTECTED] wrote on 08/08/2005 01:49:44 PM:
Sebastian wrote:
Nuno Pereira wrote:
[EMAIL PROTECTED] wrote:
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005
10:53:55 AM:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET
Sebastian wrote:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
( SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Hi. i came up with a similar query last night, but i didnt use group by.
I have question, is it better to use
Michael Stassen wrote:
Not exactly. They aren't the same. COUNT(id) counts distinct values of
id, while COUNT(*) simply counts rows. [snip]
Actually, COUNT(id) counts non-NULL values of id. COUNT(DISTINCT id)
would count distinct values.
Jasper Bryant-Greene wrote:
Michael Stassen wrote:
Not exactly. They aren't the same. COUNT(id) counts distinct values
of id, while COUNT(*) simply counts rows. [snip]
Actually, COUNT(id) counts non-NULL values of id. COUNT(DISTINCT id)
would count distinct values.
I have two tables: news and comments.
i want to count the number of comments for each newsid and update the
count in news.comment
comments.newsid belongs to news.id
can i do this with sub queries? im using v4.1.x
i have many records in the comments table so im not sure what the most
15 matches
Mail list logo
