CODEWITHSUNDEEP
oracle-databasejoinoracle11ginsert-update
stackoverflowuser95
stackoverflowuser95

Reputation: 2070

Update_or_insert (merge into) not working in Oracle

What's wrong with my UPDATE_OR_INSERT code below?

MERGE INTO EMAIL_LIST d USING (SELECT '[email protected]' 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);

Given the table:

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

Error

Error report:

SQL Error: ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected"

Upvotes: 1

Views: 3159

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

A couple of things;

You need to select the constant from something, in Oracle's case, DUAL;

MERGE INTO EMAIL_LIST d 
USING 
    (SELECT '[email protected]' 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);

Also, you cannot match on d.EMAIL and at the same time update it. Your line;

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

Doesn't really make sense anyway, since d.EMAIL is already equal to s.EMAIL or the match wouldn't happen. Remove the WHEN MATCHED and you'll end up with the working;

MERGE INTO EMAIL_LIST d 
USING 
    (SELECT '[email protected]' EMAIL FROM DUAL) s
ON (d.EMAIL = s.EMAIL)
WHEN NOT MATCHED THEN
    INSERT (EMAIL) VALUES (s.EMAIL);

Upvotes: 4

Related Questions