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 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
> efficient way to do it.
>
> i will likely run this as update every few minutes because doing a join
> on the fly is going to hurt performance when i need to display the
> comment count on the webpage i think.
>
> any help?
>
>
> table: news
> +----+-----------+
> | id | comments |
> +----+-----------+
> | 26 | 0 |
> | 21 | 0 |
> | 29 | 0 |
> +---------------
>
>
> table: comments
> +-----+--------+
> | id | newsid |
> +-----+--------+
> | 1 | 26 |
> | 2 | 21 |
> | 3 | 29 |
> | 4 | 29 |
> | 5 | 29 |
> +-------------+
>
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
