Oracle MERGE语句报错ORA-00926：缺失VALUES关键字求排查

错误原因 #

Oracle的MERGE INTO语句语法规范里，WHEN NOT MATCHED THEN分支的INSERT子句不需要重复指定INTO 目标表名——目标表已经在MERGE INTO开头声明过。原语句中额外添加了INSERT INTO TempEmployee(...)，违反语法规则，导致数据库抛出"missing VALUES keyword"错误。

修正后的正确语句 #

MERGE INTO TempEmployee TARGET
USING Consultant SOURCE
ON (TARGET.EmpId = SOURCE.EmpId)
WHEN MATCHED THEN 
    UPDATE SET TARGET.FirstName = SOURCE.FirstName, TARGET.LastName = SOURCE.LastName
WHEN NOT MATCHED THEN
  INSERT (EmpId, FirstName, LastName)
  VALUES(SOURCE.EmpId, SOURCE.FirstName, SOURCE.LastName);

关键说明 #

• 移除INSERT后的INTO TempEmployee，直接写列名列表即可
• 目标表已在MERGE INTO处明确指定，无需重复声明

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