Use of merge statements in Oracle ____oracle

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.

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, let's demonstrate 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

Two. 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;
Three. 10g 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:
The update of the 1.MERGE statement cannot modify the columns used for the connection, or it will complain (the condition column on the following is the join)
2.using later can be (SELECT Msid, AreaCode from Subs GROUP by Msid)

Reproduced