Usage of merge in Oracle

/// The merge statement is a new syntax of Oracle9i, used to merge update and insert statements. The merge statement is used to query another table based on the connection conditions of one table or subquery. The join condition matching is updated, and the insert statement cannot be executed. This syntax only requires one full table scan to complete all the work, and 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 ),
Limit number (0.00) default );

// Test Data
Insert into subs values (905310001,0, 531 );
Insert into subs values (905320001,1, 532 );
Insert into subs values (90533000533 );
Commit;
I. The following describes the basic functions of merge.
1) Use both matched and not matched clauses
Merge into Acct
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, '20170101', B. areacode );
2) there is only not matched clause, that is, only insert without updating
Merge into Acct
Using subs B on (A. msid = B. msid)
When not matched then
Insert (msid, bill_month, areacode)
Values (B. msid, '20170101', B. areacode );
3) only matched clause, that is, update is not inserted.
Merge into Acct
Using subs B on (A. msid = B. msid)
When matched then
Update set a. areacode = B. areacode

Ii. 10g medium increase 1: Conditional operation
1) Use both matched and not matched clauses
Merge into Acct
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, '20170101', B. areacode)
Where B. ms_type = 0;
2) there is only not matched clause, that is, only insert without updating
Merge into Acct
Using subs B on (A. msid = B. msid)
When not matched then
Insert (msid, bill_month, areacode)
Values (B. msid, '20170101', B. areacode)
Where B. ms_type = 0;
3) only matched clause, that is, update is not inserted.
Merge into Acct
Using subs B on (A. msid = B. msid)
When matched then
Update set a. areacode = B. areacode
Where B. ms_type = 0;
3. Enhancement 2 in 10 GB: delete operation
Merge into Acct
Using subs B on (A. msid = B. msid)
When matched then
Update set a. areacode = B. areacode
Delete where (B. ms_type! = 0 );

Note:
1. Update of the merge statement cannot modify the columns used for connection. Otherwise, an error is reported (the condition column after on is the join)
2. Using can be followed by (select msid, areacode from subs group by msid)
3. The most common error with merge occurs because the Acct record obtained through the connection condition is not unique.