ORACLE-Merge Command

You can use the Merge command to insert and update at the same time with one query. So you don't need to write a procedure which includes insert, exception and update.

Use of the command;

Merge Into Table1 T1

Using (select * from Table2 where ...) T2

On (T1.Field1 = T2.Field1 And ...)

When Matched Then

Update

Set T1.Field2 = T2.Field2

When Not Matched Then

Insert (T1.Field2) Values (T2.Field2)

Table2 is our source table. Table1 is our destination table.

The fields in the "on" clause are the unique fields for T1 and works like a where clause.