Usage of the merge statement in Oracle (reproduced)

Source of original article (http://blog.csdn.net/lichkui/article/details/4306299)

The merge statement is a new syntax for merging the UPDATE and INSERT statements oracle9i. Through the merge statement, the other table is queried based on the join criteria of one table or subquery, and the connection condition matches the update, unable to match the execution insert. This syntax only needs a full table scan to complete the whole work, the execution efficiency is higher than insert+update.

Table 1
CREATE TABLE Subs (Msid number (9),
Ms_type char (1),
AreaCode Number (3)
);
Table 2
CREATE TABLE Acct (MSID number (9),
Bill_month Number (6),
AreaCode Number (3),
Fee number (8,2) default 0.00);

Test data
INSERT into subs values (905310001,0,531);
INSERT into subs values (905320001,1,532);
INSERT into subs values (905330001,2,533);
Commit
First, the basic functions of the merge are shown below.
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 isn't 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 isn't matched then
Insert (Msid,bill_month,areacode)
VALUES (B.msid, ' 200702 ', b.areacode);
3) only matched clause, i.e. only update not inserted
Merge into Acct A
Using subs B on (A.MSID=B.MSID)
When matched then
Update Set A.areacode=b.areacode

Two. Enhanced one in 10g: 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 isn't 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 isn't matched then
Insert (Msid,bill_month,areacode)
VALUES (B.msid, ' 200702 ', B.areacode)
where b.ms_type=0;
3) only matched clause, i.e. only update not inserted
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;
Three. 10g in enhanced two: delete operation
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);

Attention:
Update of the 1.MERGE statement cannot modify the column used for the connection, or it will error (the condition column after on is the join)
2.using back can be (SELECT Msid, AreaCode from Subs GROUP by Msid)

Usage of the merge statement in Oracle (reproduced)