Oracle alternative for MySQL REPLACE INTO

Question

In MySQL we use

REPLACE INTO

to insert if a row doesn't exist and to update if it exists.

Is there a corresponding command in Oracle?

Don't use `REPLACE INTO` in MySQL, rather use `INSERT ... ON DUPLICATE KEY UPDATE`. — Quassnoi, Apr 04 '13 at 06:57
see also http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table/2692441#2692441 — user123444555621, May 02 '14 at 13:26

Quassnoi · Accepted Answer · 2016-06-26T16:40:49.043

9

MERGE
INTO    destTable d
USING   (
        SELECT  *
        FROM    sourceTable
        ) s
ON      (s.id = d.id)
WHEN NOT MATCHED THEN
INSERT  (id, destCol1, destCol2)
VALUES  (id, sourceCol1, sourceCol2)
WHEN MATCHED THEN
UPDATE
SET     destCol1 = sourceCol1,
        destCol2 = sourceCol2

edited Jun 26 '16 at 16:40

answered Apr 04 '13 at 06:56

Quassnoi

398,504
89
603
604

Do I actually need another table to merge from? Or is there a way to include the data in the command? – user123444555621 May 02 '14 at 07:29
To answer my own question, I guess it can be done using `dual`: http://www.idevelopment.info/data/Oracle/DBA_tips/SQL/SQL_5.shtml – user123444555621 May 03 '14 at 06:10

score 0 · Answer 2 · answered Apr 04 '13 at 06:54

0

You are looking for something like Merge in Oracle

Use

 Merge Into myTable s
   USING Select x from y;

See the documentation

answered Apr 04 '13 at 06:54

Scotch

3,080
11
33
48

Oracle alternative for MySQL REPLACE INTO

2 Answers2