PostgreSQL中如何使用UPDATE RETURNING结果更新关联ChannelInfo表

错误原因 #

• 你当前使用的PostgreSQL数据库不支持直接在WHERE条件的子句中嵌套UPDATE语句，该写法不符合SQL语法规范，因此触发语法报错。
• 这类「先更新A表拿返回值，再用返回值更新B表」的联动操作，需要用*公用表表达式（CTE，即WITH子句）*封装第一步的更新逻辑，再执行第二步更新。

修复后的SQL代码 #

如果更新Reporting表时仅会返回1个ChannelId，可以使用以下语句：

WITH updated_reporting AS (
    UPDATE "Reporting" 
    SET "Status"='run' 
    WHERE "Status"='a' 
    RETURNING "ChannelId"
)
UPDATE "ChannelInfo" 
SET "Amount"=CASE WHEN "Duration"='60' THEN "Amount"-12.25 ELSE "Amount"-6.13 END 
WHERE "id" = (SELECT "ChannelId" FROM updated_reporting);

如果更新Reporting表可能匹配多条记录、返回多个ChannelId，请把最后一行的=替换为IN，避免触发「单行子查询返回多行」的报错：

WITH updated_reporting AS (
    UPDATE "Reporting" 
    SET "Status"='run' 
    WHERE "Status"='a' 
    RETURNING "ChannelId"
)
UPDATE "ChannelInfo" 
SET "Amount"=CASE WHEN "Duration"='60' THEN "Amount"-12.25 ELSE "Amount"-6.13 END 
WHERE "id" IN (SELECT "ChannelId" FROM updated_reporting);

注：由于Reporting表的ChannelId是关联ChannelInfo主键的外键，以上更新操作不会出现匹配不到主键的非法数据问题。

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