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

Question

I have a table of groups, of people, and a many-to-many table of group_id,person_id pairs. groups 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, but I'm not sure how. 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`;

Answer 1

UPDATE `group`
SET `group`.`count` = (
    SELECT COUNT(*)
    FROM   `group_person`
    WHERE  `group_person`.`group_id` = `group`.`id`
)