Oracle merge enhancements from 9i to 10g

The merge statement is a new syntax of Oracle9i, used to merge update and insert statements. The insert statement cannot be executed when update is performed on the matching connection conditions. This syntax only requires one full table scan to complete all the work, and the execution efficiency is higher than insert + update.

The following is an example:

hr@ORCL> select * from p;        ID NAME---------- ----------         1 d         3 e         8 fhr@ORCL> select * from t;        ID NAME---------- ----------         1 a         2 b         3 chr@ORCL> merge into t using p  2         on (t.id=p.id)  3       when matched then  4     update set  5       t.name=p.name  6       when not matched then  7     insert values  8       (p.id,p.name)  9  ;3 rows merged.hr@ORCL> select * from t;        ID NAME---------- ----------         1 d         2 b         3 e         8 fhr@ORCL> commit;Commit complete.

Here is a slightly modified example:

hr@ORCL> insert into p values(1,'h');1 row created.hr@ORCL> commit;Commit complete.hr@ORCL> merge into t using p  2         on (t.id=p.id)  3       when matched then  4     update set  5       t.name=p.name  6       when not matched then  7     insert values  8       (p.id,p.name)  9  ; merge into t using p                    *ERROR at line 1:ORA-30926: unable to get a stable set of rows in the source tables

This error is the most common error when using merge because the records in the source table obtained through the connection condition are not unique. You need to set a key value for merge into. The update or insert into operation is determined based on the key value. Therefore, the key value must be unique in merge. Therefore, you must first select all data from the source table and the key value is unique.

Another error is also common:

hr@ORCL> create table mm (id number,name varchar2(10));Table created.hr@ORCL> create table mn (id number,name varchar2(10));Table created.hr@ORCL> insert into mm values(1,'A');1 row created.hr@ORCL> insert into mn values(1,'B');1 row created.hr@ORCL> merge into mn using mm  2         on (mn.id=mm.id)  3       when matched then  4     update set mn.id=mm.id  5       when not matched then  6     insert values  7      (mm.id,mm.name);       on (mn.id=mm.id)           *ERROR at line 2:ORA-38104: Columns referenced in the ON Clause cannot be updated: "MN"."ID"

10g enhancement 1: WHERE clause

Here is a small example:

hr@ORCL> select * from p;        ID NAME---------- ----------         1 d         2 f         7 ghr@ORCL> select * from t;        ID NAME---------- ----------         1 a         2 b         3 chr@ORCL> merge into t using p  2         on (p.id=t.id)  3      when matched then  4        update set  5          t.name=p.name  6        where p.id=2;1 row merged.hr@ORCL> select * from t;        ID NAME---------- ----------         1 a         2 f         3 c

10g enhancement 2: delete clause

hr@ORCL> select * from p;        ID NAME---------- ----------         1 d         2 f         7 ghr@ORCL> select * from t;        ID NAME---------- ----------         1 a         2 f         3 chr@ORCL> merge into t using p  2         on (p.id=t.id)  3       when matched then  4     update set       5         t.name=p.name  6     delete where  7        (t.id=2);2 rows merged.hr@ORCL> select * from t;        ID NAME---------- ----------         1 d         3 c