如何基于关联表批量更新Regions表的产品计数？

核心思路是先从Inventory表按区域分组统计产品数量，再关联Regions表完成批量更新，以下是主流数据库的实现写法：

MySQL 实现 #

仅更新有产品记录的区域 #

UPDATE regions r
JOIN (
    SELECT region_id, COUNT(*) AS product_count
    FROM inventory
    GROUP BY region_id
) i ON r.id = i.region_id
SET r.product_count = i.product_count;

更新所有区域（无产品的区域设为0） #

UPDATE regions r
LEFT JOIN (
    SELECT region_id, COUNT(*) AS product_count
    FROM inventory
    GROUP BY region_id
) i ON r.id = i.region_id
SET r.product_count = COALESCE(i.product_count, 0);

PostgreSQL 实现 #

简洁写法（自动处理无产品区域为0） #

UPDATE regions r
SET product_count = (
    SELECT COUNT(*)
    FROM inventory i
    WHERE i.region_id = r.id
);

关联子查询写法 #

UPDATE regions r
SET product_count = i.product_count
FROM (
    SELECT region_id, COUNT(*) AS product_count
    FROM inventory
    GROUP BY region_id
) i
WHERE r.id = i.region_id;

更新所有区域（无产品的区域设为0） #

UPDATE regions r
SET product_count = COALESCE(i.product_count, 0)
FROM (
    SELECT region_id, COUNT(*) AS product_count
    FROM inventory
    GROUP BY region_id
) i
WHERE r.id = i.region_id;

UPDATE regions r
SET product_count = 0
WHERE NOT EXISTS (
    SELECT 1 FROM inventory WHERE region_id = r.id
);

内容的提问来源于stack exchange，提问作者Blankman