Redirect duplicate rows to update while insert

Question

I have a insert statement on table "test". PK on column x in the table "test". Now while inserting if duplicate row comes then the same row should get updated instead insert. How can i achieve this. Is it possible by dup_val_on_index?

Please help.

First define "duplicate". Then check Oracle's [merge](http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm) that can do either insert or update depending if the row already exists in the target table. — user272735, Dec 30 '14 at 06:00
here both insert and updae is on the same table.if passible can you please provide me an example — user3195273, Dec 30 '14 at 06:23

score 0 · Answer 1 · edited May 23 '17 at 10:33

First create a copy of the above table without any KEY Columns and follow

Step 1: truncate the table first whenever you encounter a bunch of insert statement comes

Step 2: INSERT the above truncated tables

Step 3: Execute the MERGE statement like below

MERGE INTO TABLE_MAIN M
   USING TABLE_MAIN_COPY C
   ON (m.id = c.id)
   WHEN MATCHED THEN UPDATE SET M.somecol = c.somecol
   WHEN NOT MATCHED THEN INSERT (m.id, m.somecol)
     VALUES (c.id, c.somecol);

You may incur error while on merger ORA-30926: unable to get a stable set of rows in the source tables when there is two or more rows while on update.

you may avoid that using the GROUP function related to id or like ORA-30926: unable to get a stable set of rows in the source tables

Redirect duplicate rows to update while insert

1 Answers1