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`;
UPDATE `group`
SET `group`.`count` = (
SELECT COUNT(*)
FROM `group_person`
WHERE `group_person`.`group_id` = `group`.`id`
)