UPDATE with GROUP BY

hi,


consider tables

1. blogs (id, num_article)
2. articles (id, blog_id)

is it possible to perform the query in the following statement?

update blogs, articles set blogs.num_article = count(*) where blogs.id
= articles.blog_id group by articles.blog_id

thanks.

howachen@gmail.com wrote:
> hi,
>
>
> consider tables
>
> 1. blogs (id, num_article)
> 2. articles (id, blog_id)
>
> is it possible to perform the query in the following statement?
>
> update blogs, articles set blogs.num_article = count(*) where blogs.id
> = articles.blog_id group by articles.blog_id
>
> thanks.

This'll do it:

UPDATE blogs,
(
SELECT blog_id, count( * ) AS ac
FROM articles
GROUP BY blog_id
) AS t1
SET blogs.num_article = t1.ac WHERE blogs.id = t1.blog_id

howachen@gmail.com wrote:
> update blogs, articles set blogs.num_article = count(*) where blogs.id
> = articles.blog_id group by articles.blog_id

No, there is no support for performing GROUP BY or aggregate
calculations in an UPDATE statement. You can use a subquery if you have
MySQL 4.1 or higher:

update blogs set blogs.num_article = (select count(*) from articles
where blogs.id = articles.blog_id);

Note that it's unnecessary to GROUP BY the column in this case, since
the subquery is calculated for each row of the blogs table, and so it is
naturally for exactly one value of blog_id in each iteration.

Another option is to do the update incrementally, instead of calculating
the count():

update blogs set num_articles = 0;

update blogs, articles
set blogs.num_article = blogs.num_article + 1
where blogs.id = articles.blog_id

This should get the same result.

Regards,
Bill K.