MySQL中子查询返回重复父表ID时如何实现父表字段多次更新

问题根因 #

原SQL中IN子句会对匹配的ID去重，每个符合条件的ChannelInfo行只会被执行一次更新，和子查询返回该ID的次数无关，因此无法实现按重复次数扣减的需求。

解决代码 #

WITH u AS (
    UPDATE "Reporting"
        SET "Status" = 'run'
        WHERE "Status" = 'a'
    RETURNING "ChannelId"
),
-- 统计每个ChannelId需要扣减的次数
channel_deduct_count AS (
    SELECT "ChannelId", COUNT(*) AS deduct_times
    FROM u
    GROUP BY "ChannelId"
)
UPDATE "ChannelInfo" c
SET "Amount" = c."Amount" - (
    CASE 
        WHEN c."Duration" = '60' THEN 12.25 * dc.deduct_times
        ELSE 6.13 * dc.deduct_times
    END
)
FROM channel_deduct_count dc
WHERE c."id" = dc."ChannelId";

逻辑说明 #

• 新增的channel_deduct_count子查询会对u返回的ChannelId做分组统计，得到每个ID对应的待扣减次数
• 更新ChannelInfo时直接关联统计结果，用单次扣减金额乘以扣减次数得到总扣减值，一次更新即可完成对应次数的扣减，性能优于循环更新
• 整个操作和原逻辑一致，都在同一个事务内完成，不会出现并发导致的数据不一致问题

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