Oracle 12c执行MERGE语句报ORA-02012缺失USING关键字错误如何解决

核心错误原因 #

• 表别名不允许加AS关键字：Oracle语法中声明表别名不需要加AS，直接跟在表名后即可，原代码中PATRON_STATISTICS AS Target、PATRON AS Source的写法是语法错误，这是触发ORA-02012: missing USING keyword的直接原因。
• WHEN NOT MATCHED不支持BY Target后缀：Oracle的MERGE语法中，WHEN NOT MATCHED默认指代源表存在、目标表不存在匹配项的场景，不需要额外加BY Target修饰。
• INSERT值缺少源表前缀：VALUES子句中的Last_Name未加Source.前缀，会触发字段歧义报错。

修正后的完整代码 #

MERGE INTO PATRON_STATISTICS Target
USING PATRON Source 
ON (Source.PATRON_id = Target.PATRON_id)
WHEN NOT MATCHED THEN
    INSERT (Patron_id, First_name , Last_Name, Number_of_Items, Number_of_Purchases)
    VALUES (Source.Patron_id, Source.First_name, Source.Last_Name, 0, 0)
WHEN MATCHED AND Source.Last_Name <> Target.Last_Name THEN 
    UPDATE 
        SET Target.First_name = Source.First_name,
            Target.Last_Name = Source.Last_Name;

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