PostgreSQL使用CTE实现跨表依赖插入更新B表关联ID问题求解

你原有写法的核心错误有3处：

1. 更新目标表错误，你写了UPDATE A，实际需要更新的是B表
2. coalesce 中嵌套子查询的语法不符合规范
3. 带特殊符号的字段user-id未加引号，会被数据库识别为减法运算

可直接运行的SQL代码（适配PostgreSQL） #

WITH cte_existing AS (
    -- 查询Table A中已有的符合条件的记录id
    SELECT id 
    FROM A 
    WHERE married = false 
      AND salaried = false
),
cte_insert AS (
    -- 符合条件的记录不存在时插入新记录，返回新生成的id
    INSERT INTO A (married, salaried)
    SELECT false, false
    WHERE NOT EXISTS (SELECT 1 FROM cte_existing)
    RETURNING id
),
cte_target_aid AS (
    -- 合并结果，保证能拿到唯一可用的A表id
    SELECT id FROM cte_existing
    UNION ALL
    SELECT id FROM cte_insert
)
-- 更新Table B的对应记录
UPDATE B
SET id_a = (SELECT id FROM cte_target_aid)
WHERE "user-id" = 55;

逻辑说明 #

• 若Table A中已经存在married=false且salaried=false的记录，cte_existing会返回对应id，cte_insert不会执行插入，最终直接用已有id更新Table B
• 若Table A中没有符合条件的记录，cte_insert会插入新记录并返回新id，最终用新id更新Table B
• 完全符合你要求的执行逻辑，最终运行结果和你给出的预期数据一致

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