Oracle SQL带WHERE条件复制列值时缺失逗号报错咨询

你遇到的缺失逗号报错，核心原因是Oracle不支持UPDATE ... FROM ...多表关联的语法（这是MySQL、PostgreSQL等数据库的写法），直接用这类写法会触发语法解析错误。以下是两种可直接运行的正确写法：

推荐方案：使用MERGE语句（无语法歧义，支持复杂关联） #

MERGE INTO Label_Cnfg t1
USING (
    SELECT TYPE, HDR_LBL_SQL 
    FROM Label_Cnfg 
    WHERE WHSE = 2
) t2
ON (
    t1.TYPE = t2.TYPE 
    AND t1.WHSE = 1 
    AND t1.HDR_LBL_SQL IS NULL
)
WHEN MATCHED THEN 
UPDATE SET t1.HDR_LBL_SQL = t2.HDR_LBL_SQL;

备选方案：标准UPDATE子查询写法 #

UPDATE Label_Cnfg t1
SET t1.HDR_LBL_SQL = (
    SELECT t2.HDR_LBL_SQL 
    FROM Label_Cnfg t2 
    WHERE t2.WHSE = 2 AND t2.TYPE = t1.TYPE
)
WHERE t1.WHSE = 1
AND t1.HDR_LBL_SQL IS NULL
-- 避免WHSE=2没有对应TYPE记录时被更新为NULL
AND EXISTS (
    SELECT 1 
    FROM Label_Cnfg t2 
    WHERE t2.WHSE = 2 AND t2.TYPE = t1.TYPE
);

执行更新前建议先运行以下查询，确认待更新的行数符合预期，避免误操作：

SELECT COUNT(*) AS 待更新记录数
FROM Label_Cnfg t1
WHERE t1.WHSE = 1
AND t1.HDR_LBL_SQL IS NULL
AND EXISTS (
    SELECT 1 
    FROM Label_Cnfg t2 
    WHERE t2.WHSE = 2 AND t2.TYPE = t1.TYPE
);

确认无误后再执行更新操作，生产环境建议先开启事务，验证更新结果正确后再提交。

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