Update_or_insert (合并) 在 Oracle 中不工作

标签: oracle11g Oracle
发布时间: 2017/4/15 18:58:34
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我下面的 UPDATE_OR_INSERT 代码有什么错误?

MERGE INTO EMAIL_LIST d USING (SELECT 'foo@gmail.com' EMAIL) s
    ON (d.EMAIL = s.EMAIL)
        WHEN MATCHED THEN
            UPDATE SET d.EMAIL = s.EMAIL
        WHEN NOT MATCHED THEN
            INSERT (EMAIL) VALUES (s.EMAIL);

给定的表︰

CREATE TABLE EMAIL_LIST ( 
    EMAIL VARCHAR2 (100)  NOT NULL
);
ALTER TABLE EMAIL_LIST
    ADD CONSTRAINT PK_EMAIL_LIST PRIMARY KEY ( EMAIL ) ;

错误

错误报告︰

SQL 错误︰ ORA-00923︰ 从哪里找不到关键字预期 00923。00000-"从关键字预期位置找不到"

解决方法 1:

几件事情;

您需要选择该常数的东西,在 Oracle 的情况下,双;

MERGE INTO EMAIL_LIST d 
USING 
    (SELECT 'foo@gmail.com' EMAIL FROM DUAL) s
ON (d.EMAIL = s.EMAIL)
WHEN MATCHED THEN
    UPDATE SET d.EMAIL = s.EMAIL
WHEN NOT MATCHED THEN
    INSERT (EMAIL) VALUES (s.EMAIL);

此外,您不能匹配上 d.EMAIL 和在同一时间更新它。你的线;

WHEN MATCHED THEN
    UPDATE SET d.EMAIL = s.EMAIL

根本没道理不管怎么说,因为 d.EMAIL 是已经等于 s.EMAIL 或不会发生的这场比赛。删除 WHEN MATCHED 和你最终会与工作;

MERGE INTO EMAIL_LIST d 
USING 
    (SELECT 'foo@gmail.com' EMAIL FROM DUAL) s
ON (d.EMAIL = s.EMAIL)
WHEN NOT MATCHED THEN
    INSERT (EMAIL) VALUES (s.EMAIL);
官方微信
官方QQ群
31647020