Oracle中如何使用一个CTE更新另一个CTE的列？

原SQL的错误原因 #

1. 语法结构违规：Oracle中WITH子句定义后只能执行一条DML（如UPDATE、MERGE）或SELECT语句，你在UPDATE后又写了SELECT，导致语法错误。
2. CTE不可直接更新：普通CTE是临时查询结果集，Oracle不支持直接UPDATE这类CTE；即使是可更新的CTE（基于单表无复杂逻辑），原写法的结构也不符合规则。
3. 列缺失错误：你的cte_B只查询了Y列，后续WHERE条件里引用CTE_B.ID会提示列不存在。

正确解决方案 #

情况1：需要更新原表table_1的Y列 #

如果要永久修改原表数据，推荐直接更新原表，结合CTE简化逻辑：

WITH cte_B AS (
    SELECT ID, Y FROM table_2  -- 必须包含关联用的ID列
)
UPDATE table_1 t1
SET Y = (SELECT b.Y FROM cte_B b WHERE b.ID = t1.ID)
WHERE EXISTS (SELECT 1 FROM cte_B b WHERE b.ID = t1.ID); -- 可选，避免更新无匹配的行

-- 查询更新后的结果
SELECT * FROM table_1;

如果你的cte_A有复杂逻辑需要保留，也可以用MERGE（需确保cte_A是可更新的，即基于单表无聚合、DISTINCT等）：

WITH cte_A AS (
    SELECT ID, X, Y FROM table_1  -- 保留你的复杂逻辑，比如原本的0 AS Y可以调整
),
cte_B AS (
    SELECT ID, Y FROM table_2
)
MERGE INTO cte_A a
USING cte_B b
ON (a.ID = b.ID)
WHEN MATCHED THEN UPDATE SET a.Y = b.Y;

SELECT * FROM table_1;

情况2：仅需获取更新后的临时结果（不修改原表） #

如果不需要修改原表，只是想得到合并后的结果集，直接用JOIN查询即可，无需UPDATE：

WITH cte_A AS (
    SELECT ID, X, 0 AS Y FROM table_1  -- 保留你的复杂逻辑
),
cte_B AS (
    SELECT ID, Y FROM table_2
)
SELECT 
    a.ID,
    a.X,
    NVL(b.Y, a.Y) AS Y  -- 有匹配则用cte_B的Y，否则保留cte_A的Y
FROM cte_A a
LEFT JOIN cte_B b ON a.ID = b.ID;

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