ORACLE ten g Merge into usage

Prior to Oracle 10g, the merge statement supported 2 simple usages for matching updates and mismatches, and in 10g Oracle enhanced the merge statement by adding conditional options and delete operations. Below I have a demo to briefly describe the 10g merge enhancements and the use of the 10g pre-merge.
　
Refer to Oracle's SQL Reference below I'll do a test in the environment.

Creating Tables Subs and Acct

CREATE TABLE Subs (       msid number     (9),       ms_type  char (1),       AreaCode number (3)), CREATE TABLE Acct (       Msid Number (       9),       bill_month number (6),       areacode number   (3),       fee number (        8,2) default 0.00) ;

Inserting data

INSERT into subs values (905310001,0,531), insert into subs values (905320001,1,532), insert into subs values ( 905330001,2,533); commit

Grammar

--  grammar merge [into [Schema.] table [T_alias] using [schema.] {Table | view | subquery} [T_alias]  On (condition) when matched and merge_update_clause when not             matched then             merge_insert_clause;

Test

---  matched: Update not    matched: Insert  Both can be executed synchronously or as long as a condition merge into Acct a   using subs B on      (A.msid = b.msid) C5/>when matched then            update set A.areacode = If not          matched then            insert (Msid, Bill_month, AreaCode) Val UEs (B.msid, ' 200702 ', b.areacode); commit

Enhanced conditional query Operations

Merge into Acct a   using subs B in      (A.msid = b.msid) When         matched then            update set A.areacode = B.ms_type = 0 When not         matched then            insert (Msid, Bill_month, AreaCode) VALUES (b.msid, ' 200702 ', B.areacode) W Here B.ms_type = 0;commit

Enhanced Delete operations

Merge into Acct a   using subs B in      (A.msid = b.msid) When         matched then            update set a.areacode =            delet e WHERE (B.ms_type! = 0); commit

ORACLE ten g Merge into usage