Updating A Table Based On The Count() From Its Many-to-Many Table

Go To StackoverFlow.com

0

I have a table of groups, of people, and a many-to-many table of group_id, person_id pairs. group has a count column which should store the current number of people in each group. I'd like to update this information in one SQL command if I can. I imagine some kind of subquery would let me accomplish this, but I'm not sure how.

This query successfully gives me a mapping of group_ids to their count

SELECT `group_id`, COUNT(`group_id`) FROM `group-person` GROUP BY `group_id`;

This query fails, but if it worked, this would be what I'm trying to do

UPDATE `group`,`group-person` WHERE `group`.`id` = `group-person`.`group_id`
SET `group`.`count` = COUNT(`group-person`.`group_id`)
GROUP BY `group-person`.`group_id`;
2009-06-16 08:21
by dimo414
you cant do GROUP BY in update query - Haim Evgi 2009-06-16 08:27
Yeah, I know, the query fails, it was just how I imagined it could possibly work - dimo414 2009-06-16 08:32


2

UPDATE `group`
SET `group`.`count` = (
    SELECT COUNT(*)
    FROM   `group_person`
    WHERE  `group_person`.`group_id` = `group`.`id`
)
2009-06-16 08:25
by Steve Weet
Wow, I'm impressed, that was lightning fast - dimo414 2009-06-16 08:30
I'm afraid that's what hapens when you remember the 'SQL-86' Standard - Steve Weet 2009-06-16 08:33