MySQL 批量 UPDATE 關聯子查詢：從統計表更新主表計數欄位

場景

表 EventGroup 有欄位 joinCount，需根據 EventGroupMember 滿足條件的記錄數更新。

單條 SQL 實現

UPDATE EventGroup g
SET joinCount = (
    SELECT b.counts
    FROM (
        SELECT eventGroupId, SUM(1) AS counts
        FROM EventGroupMember
        WHERE groupRoleId < 4
          AND (memberStatus = 0 OR memberStatus = 3)
        GROUP BY eventGroupId
    ) b
    WHERE b.eventGroupId = g.eventGroupId
)
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT eventGroupId, SUM(1) AS counts
        FROM EventGroupMember
        WHERE groupRoleId < 4
          AND (memberStatus = 0 OR memberStatus = 3)
        GROUP BY eventGroupId
    ) b
    WHERE b.eventGroupId = g.eventGroupId
);

優化建議

1. 子查詢重複，建議用 WITH (CTE, 8.0+) 或衍生表 JOIN：

WITH stats AS (
  SELECT eventGroupId, COUNT(*) AS cnt
  FROM EventGroupMember
  WHERE groupRoleId < 4 AND memberStatus IN (0,3)
  GROUP BY eventGroupId
)
UPDATE EventGroup g
JOIN stats s ON g.eventGroupId = s.eventGroupId
SET g.joinCount = s.cnt;

2. EventGroupMember(eventGroupId, groupRoleId, memberStatus) 建立複合索引加速聚合。
3. 大量更新時分批次 (每批 1000-5000 行) 避免長鎖表。