The usage of merage in DB2

The merge statement is often used in the conversion and loading processes in the Data warehouse, and here is a quick summary.

The merge statement is a new syntax for merging update and INSERT statements oracle9i. Through the merge statement, according to a table or the join conditions of a subquery to query the other table, join condition matching for update, unable to match the execution of the insert. This syntax only needs a full table scan to complete all the work, execution efficiency is higher than insert+update.
Here's a concrete example:
Sql> CREATE TABLE T as SELECT rownum ID, a.* from Dba_objects A;
Table has been created.
Sql> CREATE TABLE T1 as
2 SELECT rownum ID, OWNER, TABLE_NAME, CAST (' TABLE ' as VARCHAR2 ()) object_type
3 from Dba_tables;
Table has been created.
Sql> MERGE into T1 USING T
2 on (T.owner = T1. OWNER and t.object_name = T1. table_name and T.object_type = T1. object_type)
3 when matched THEN UPDATE SET t1.id = t.id
4 when not matched THEN INSERT VALUES (t.id, T.owner, T.object_name, T.object_type);
6165 lines have been merged.
Sql> SELECT ID, OWNER, object_name, object_type from T
2 minus
3 SELECT * from T1;
No rows selected
The merge syntax is actually very simple, and here's a little bit of an example.
sql> DROP TABLE T;
The table has been discarded.
sql> DROP TABLE T1;
The table has been discarded.
Sql> CREATE TABLE T as SELECT rownum ID, a.* from Dba_objects A;
Table has been created.
Sql> CREATE TABLE T1 as SELECT rownum ID, OWNER, table_name from Dba_tables;
Table has been created.
Sql> MERGE into T1 USING T
2 on (T.owner = T1. OWNER and t.object_name = T1. TABLE_NAME)
3 when matched THEN UPDATE SET t1.id = t.id
4 when not matched THEN INSERT VALUES (t.id, T.owner, t.object_name);
MERGE into T1 USING T
*
ERROR is on line 1th:
ORA-30926: Unable to get a stable set of rows in the source table
This error is the most common error in using the merge, which is caused by the fact that the record of T is not unique through the join condition. The simplest solution is similar:
Sql> MERGE into T1
2 USING (SELECT OWNER, object_name, MAX (ID) ID from T GROUP by OWNER, object_name) t
3 on (T.owner = T1. OWNER and t.object_name = T1. TABLE_NAME)
4 when matched THEN UPDATE SET t1.id = t.id
5 When not matched THEN INSERT VALUES (t.id, T.owner, t.object_name);
5775 lines have been merged.
In addition, the update of the merge statement cannot modify the columns used for the connection, otherwise it will be an error, as the details can refer to: http://blog.itpub.net/post/468/14844

===============================================================

ref:http://tomszrp.itpub.net/post/11835/263865

Prior to Oracle 10g, the merge statement supported 2 simple usages of matching updates and mismatched inserts, and Oracle enhanced the merge statement in 10g, adding conditional options and delete operations. Below I have a demo to briefly introduce the enhanced 10g merge and 10g before the use of the merge.

　

Referring to Oracle's SQL Reference, you can see the syntax for the merge statement as follows:
MERGE [hint] into [schema.] table [T_alias] USING [schema.]
{Table | view | subquery} [T_alias] On (condition)
When matched THEN Merge_update_clause
When not matched THEN merge_insert_clause;

Below I do a test on the Windows XP 10.2.0.1 version to see

Sql> select * from V$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-prod
Pl/sql Release 10.2.0.1.0-production
CORE 10.2.0.1.0 Production
TNS for 32-bit windows:version 10.2.0.1.0-production
Nlsrtl Version 10.2.0.1.0-production

Sql>
First, create a test table
Sql> CREATE TABLE Subs (Msid number (9),
2 Ms_type char (1),
3 AreaCode Number (3)
4);

Table has been created.

Sql> CREATE TABLE Acct (MSID number (9),
2 Bill_month Number (6),
3 AreaCode Number (3),
4 fee number (8,2) default 0.00);

Table has been created.

Sql>
Sql> INSERT INTO subs values (905310001,0,531);

1 lines have been created.

Sql> INSERT INTO subs values (905320001,1,532);

1 lines have been created.

Sql> INSERT INTO subs values (905330001,2,533);

1 lines have been created.

Sql> commit;

Submit completed.

Sql>
　

Two, the following first demonstration of the basic functions of the merge

1) matched and not matched clauses used simultaneously
Merge into Acct A
Using subs B on (A.MSID=B.MSID)

When matched then
Update Set A.areacode=b.areacode

When not matched then
Insert (Msid,bill_month,areacode)
VALUES (B.msid, ' 200702 ', b.areacode);

2 only not matched clause, that is, only insert not update
Merge into Acct A
Using subs B on (A.MSID=B.MSID)

When not matched then
Insert (Msid,bill_month,areacode)
VALUES (B.msid, ' 200702 ', b.areacode);

3 only matched clause, that is, only update does not insert
Merge into Acct A
Using subs B on (A.MSID=B.MSID)

When matched then
Update Set A.areacode=b.areacode

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as study

Sql> select * from subs;

Msid Ms_type AreaCode
---------- ------- --------
905310001 0 531
905320001 1 532
905330001 2 533

Sql> SELECT * from Acct;

Msid Bill_month AreaCode FEE
---------- ---------- -------- ----------

Sql>
Sql> Merge into Acct a
2 using subs B on (A.MSID=B.MSID)
3 when matched then
4 Update Set A.areacode=b.areacode
5 When not matched then
6 Insert (Msid,bill_month,areacode)
7 values (B.msid, ' 200702 ', b.areacode);

Done

Sql> SELECT * from Acct;

Msid Bill_month AreaCode FEE
---------- ---------- -------- ----------
905320001 200702 532 0.00
905330001 200702 533 0.00
905310001 200702 531 0.00

Sql> INSERT INTO subs values (905340001,3,534);

1 row inserted

Sql> select * from subs;

Msid Ms_type AreaCode
---------- ------- --------
905340001 3 534
905310001 0 531
905320001 1 532
905330001 2 533

Sql>
Sql> Merge into Acct a
2 using subs B on (A.MSID=B.MSID)
3 When not matched then
4 Insert (Msid,bill_month,areacode)
5 VALUES (b.msid, ' 200702 ', b.areacode);

Done

Sql> SELECT * from Acct;

Msid Bill_month AreaCode FEE
---------- ---------- -------- ----------
905320001 200702 532 0.00
905330001 200702 533 0.00
905310001 200702 531 0.00
905340001 200702 534 0.00

Sql> Update subs set areacode=999;

4 rows Updated

Sql> select * from subs;

Msid Ms_type AreaCode
---------- ------- --------
905340001 3 999
905310001 0 999
905320001 1 999
905330001 2 999

Sql> SELECT * from Acct;

Msid Bill_month AreaCode FEE
---------- ---------- -------- ----------
905320001 200702 532 0.00
905330001 200702 533 0.00
905310001 200702 531 0.00
905340001 200702 534 0.00

Sql>
Sql> Merge into Acct a
2 using subs B on (A.MSID=B.MSID)
3 when matched then
4 Update set A.areacode=b.areacode;

Done

Sql> SELECT * from Acct;

Msid Bill_month AreaCode FEE
---------- ---------- -------- ----------
905320001 200702 999 0.00
905330001 200702 999 0.00
905310001 200702 999 0.00
905340001 200702 999 0.00

Sql>
　
Three, 10g enhanced one: conditional operation

1) matched and not matched clauses used simultaneously
Merge into Acct A
Using subs B on (A.MSID=B.MSID)
When matched then
Update Set A.areacode=b.areacode
where b.ms_type=0
When not matched then
Insert (Msid,bill_month,areacode)
VALUES (B.msid, ' 200702 ', B.areacode)
where b.ms_type=0;
2 only not matched clause, that is, only insert not update
Merge into Acct A
Using subs B on (A.MSID=B.MSID)
When not matched then
Insert (Msid,bill_month,areacode)
VALUES (B.msid, ' 200702 ', B.areacode)
where b.ms_type=0;

3 only matched clause, that is, only update does not insert
Merge into Acct A
Using subs B on (A.MSID=B.MSID)
When matched then
Update Set A.areacode=b.areacode
where b.ms_type=0;


Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as study

Sql> select * from subs;

Msid Ms_type AreaCode
---------- ------- --------
905310001 0 531
905320001 1 532
905330001 2 533

Sql> SELECT * from Acct;

Msid Bill_month AreaCode FEE
---------- ---------- -------- ----------

Sql>
Sql> Merge into Acct a
2 using subs B on (A.MSID=B.MSID)
3 when matched then
4 Update Set A.areacode=b.areacode
5 Where b.ms_type=0
6 when not matched then
7 Insert (Msid,bill_month,areacode)
8 VALUES (b.msid, ' 200702 ', B.areacode)
9 where b.ms_type=0;

Done

Sql> SELECT * from Acct;

Msid Bill_month AreaCode FEE
---------- ---------- -------- ----------
905310001 200702 531 0.00

Sql> INSERT INTO subs values (905360001,0,536);

1 row inserted

Sql> select * from subs;

Msid Ms_type AreaCode
---------- ------- --------
905360001 0 536
905310001 0 531
905320001 1 532
905330001 2 533

Sql>
Sql> Merge into Acct a
2 using subs B on (A.MSID=B.MSID)
3 When not matched then
4 Insert (Msid,bill_month,areacode)
5 VALUES (b.msid, ' 200702 ', B.areacode)
6 where b.ms_type=0;

Done

Sql> SELECT * from Acct;

Msid Bill_month AreaCode FEE
---------- ---------- -------- ----------
905310001 200702 531 0.00
905360001 200702 536 0.00

Sql> Update subs set areacode=888 where ms_type=0;

2 rows Updated

Sql> select * from subs;

Msid Ms_type AreaCode
---------- ------- --------
905360001 0 888
905310001 0 888
905320001 1 532
905330001 2 533

Sql> SELECT * from Acct;

Msid Bill_month AreaCode FEE
---------- ---------- -------- ----------
905310001 200702 531 0.00
905360001 200702 536 0.00

Sql>
Sql> Merge into Acct a
2 using subs B on (A.MSID=B.MSID)
3 when matched then
4 Update Set A.areacode=b.areacode
5 where b.ms_type=0;

Done

Sql> SELECT * from Acct;

Msid Bill_month AreaCode FEE
---------- ---------- -------- ----------
905310001 200702 888 0.00
905360001 200702 888 0.00

Sql>
Four, 10g enhanced two: delete operation
An optional DELETE WHERE clause can is used to clean up a
Merge operation. Only those rows which match both the in clause
and the DELETE WHERE clause are deleted.

Merge into Acct A
Using subs B on (A.MSID=B.MSID)
When matched then
Update Set A.areacode=b.areacode
Delete where (b.ms_type!=0);

Sql> select * from subs;

Msid Ms_type AreaCode
---------- ------- --------
905310001 0 531
905320001 1 532
905330001 2 533

Sql> SELECT * from Acct;

Msid Ms_type AreaCode
---------- ------- --------
905310001 0 531
905320001 1 532
905330001 2 533

Sql>
Sql> Merge into Acct a
2 using subs B on (A.MSID=B.MSID)
3 when matched then
4 Update Set A.areacode=b.areacode
5 Delete where (b.ms_type!=0);

Done

Sql> SELECT * from Acct;

Msid Ms_type AreaCode
---------- ------- --------
905310001 0 531

Sql>

For more detailed syntax, refer to the Oracle SQL reference manual.