I have a table of groups, of people, and a many-to-many table of
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` )