Oracle中用TABLE2数据更新TABLE1的SQL语句报错求助

原语句的错误点 #

• Oracle不支持类似SQL Server的UPDATE ... FROM ... JOIN语法，这是报错的核心原因
• 子查询语法错误：SELECT t1.S, t1.D) AS doxa多了一个多余的右括号，正确的别名定义应为SELECT t1.S, t1.D AS doxa
• 关联条件中出现未定义的别名c.S，此处应使用外层表别名TPR.S
• 末尾WHERE TPR.S = result.S与JOIN条件重复，属于冗余代码

正确的实现方式 #

方法1：UPDATE结合子查询 #

UPDATE TABLA1 TPR
SET TPR.doxa = (
    SELECT t1.D
    FROM TABLA1 t1 
    INNER JOIN TABLA2 t2 ON t1.S = t2.D 
    WHERE t1.S = TPR.S
)
WHERE EXISTS (
    SELECT 1
    FROM TABLA1 t1 
    INNER JOIN TABLA2 t2 ON t1.S = t2.D 
    WHERE t1.S = TPR.S
);

WHERE EXISTS用于避免将无匹配记录的TPR.doxa更新为NULL，若无需该限制可直接去掉此条件。

方法2：使用MERGE语句（推荐，逻辑更直观） #

MERGE是Oracle专为合并/关联更新设计的语句，适配此类场景：

MERGE INTO TABLA1 TPR
USING (
    SELECT t1.S, t1.D AS doxa
    FROM TABLA1 t1 
    INNER JOIN TABLA2 t2 ON t1.S = t2.D 
) result
ON (TPR.S = result.S)
WHEN MATCHED THEN
    UPDATE SET TPR.doxa = result.doxa;

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