Usage of MERGE statements in Oracle

In Oracle, MERGE statements are 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. Merge into is also a dml statement. Like other dml statements, you need to end the transaction through rollback and commit. // 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), comment 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; 1. the following describes the basic functions of merge. 1) Both matched and not matched clses use merge into acct a us. Ing subs B on (. msid = B. msid) when MATCHED then update set. areacode = B. areacode when not matched then insert (msid, bill_month, areacode) values (B. msid, '000000', B. areacode); 2) Only not matched clause, that is, insert only merge into acct a using subs B on (. msid = B. msid) when not matched then insert (msid, bill_month, areacode) values (B. msid, '000000', B. areacode); 3) there is only matched clause, that is, only update without inserting merge into acct a using Subs B on (. msid = B. msid) when MATCHED then update set. areacode = B. areacode 2. 10g increase 1: Condition operation 1) matched and not matched clauses use merge into acct a using subs B on (. msid = B. msid) when MATCHED then update set. areacode = B. areacode where B. ms_type = 0 when not matched then insert (msid, bill_month, areacode) values (B. msid, '000000', B. areacode) where B. ms_type = 0; 2) there is only not matched clause, that is, only insert without updating merge Acct a using subs B on (. msid = B. msid) when not matched then insert (msid, bill_month, areacode) values (B. msid, '000000', B. areacode) where B. ms_type = 0; 3) only matched clause is available, that is, only merge into acct a using subs B on (. msid = B. msid) when MATCHED then update set. areacode = B. areacode where B. ms_type = 0; 3. enhancement 2 in 10g: delete operation merge into acct a using subs B on (. msid = B. msid) when MATCHED then update set. 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 join. using can be followed by (SELECT msid, areacode FROM subs GROUP by msid)