Oracle MERGE语句语法错误排查及修正（ORA-00969）

错误核心原因 #

你的MERGE语句违反了Oracle MERGE的语法规范：

• WHEN MATCHED THEN分支仅允许执行UPDATE操作，而你在该分支下写了INSERT，导致数据库解析器无法正确识别语句结构，进而抛出了看似与ON关键字相关的误报错误。

分场景修正方案 #

场景1：当源表存在匹配记录时，向目标表插入数据 #

如果需求是从AI_PRODUCT_IMAGE表中筛选出IMAGE_ID='172c86a216304dbwks02'且ZONE='03'的记录，并将相关数据插入到AI_IMAGE_EVENT表，无需使用MERGE，直接用INSERT...SELECT语句即可：

INSERT INTO AI_IMAGE_EVENT (
    AI_IMAGE_EVENT_SQID,
    AI_PRODUCT_IMAGE_SQID,
    AI_EVENT_TYPE,
    AI_PROC_START_TIME,
    AI_PROC_END_TIME,
    AI_IMAGE_SERV_TIME,
    FACILITY_ID,
    ZONE,
    CREATED_DATE,
    CREATED_BY,
    LAST_UPDATED_DATE,
    LAST_UPDATED_BY
)
SELECT 
    '910000008201',
    API.AI_PRODUCT_IMAGE_SQID,
    2,
    SYSDATE,
    SYSDATE,
    NULL,
    '68731',
    10,
    SYSDATE,
    '68731',
    SYSDATE,
    '68731'
FROM AI_PRODUCT_IMAGE API
WHERE API.IMAGE_ID = '172c86a216304dbwks02'
  AND API.ZONE = '03';

场景2：匹配源表与目标表，匹配则更新、不匹配则插入 #

如果真实需求是将源表数据同步到目标表：当AI_IMAGE_EVENT中存在与AI_PRODUCT_IMAGE匹配的记录时更新目标表，不存在则插入，需修正MERGE语法结构：

1. 补充ON子句中目标表与源表的关联条件（示例以AI_PRODUCT_IMAGE_SQID关联）
2. 将INSERT移到WHEN NOT MATCHED THEN分支，WHEN MATCHED THEN分支改为UPDATE

示例语句：

MERGE INTO AI_IMAGE_EVENT AIE
USING AI_PRODUCT_IMAGE API
    ON AIE.AI_PRODUCT_IMAGE_SQID = API.AI_PRODUCT_IMAGE_SQID -- 补充目标表与源表的关联条件
    AND API.IMAGE_ID = '172c86a216304dbwks02'
    AND API.ZONE = '03'
WHEN MATCHED THEN
    UPDATE SET -- 匹配时执行更新操作
        AI_EVENT_TYPE = 2,
        AI_PROC_START_TIME = SYSDATE,
        AI_PROC_END_TIME = SYSDATE,
        FACILITY_ID = '68731',
        ZONE = 10,
        LAST_UPDATED_DATE = SYSDATE,
        LAST_UPDATED_BY = '68731'
WHEN NOT MATCHED THEN
    INSERT ( -- 不匹配时执行插入操作
        AI_IMAGE_EVENT_SQID,
        AI_PRODUCT_IMAGE_SQID,
        AI_EVENT_TYPE,
        AI_PROC_START_TIME,
        AI_PROC_END_TIME,
        AI_IMAGE_SERV_TIME,
        FACILITY_ID,
        ZONE,
        CREATED_DATE,
        CREATED_BY,
        LAST_UPDATED_DATE,
        LAST_UPDATED_BY
    )
    VALUES (
        '910000008201',
        API.AI_PRODUCT_IMAGE_SQID,
        2,
        SYSDATE,
        SYSDATE,
        NULL,
        '68731',
        10,
        SYSDATE,
        '68731',
        SYSDATE,
        '68731'
    );

补充说明 #

Oracle MERGE语句的标准语法结构为：

MERGE INTO target_table
USING source_table
ON (join_condition)
WHEN MATCHED THEN
    UPDATE SET column1 = value1, ...
WHEN NOT MATCHED THEN
    INSERT (column1, ...) VALUES (value1, ...);

必须严格遵循MATCHED分支对应UPDATE、NOT MATCHED分支对应INSERT的规则。

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