Using the merge statement, here's a quick summary (a statement resolves the update and insert features)

The merge statement is often used in the conversion and loading processes in the Data warehouse, and here is a quick summary.

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.
Here's a concrete example:
Sql> CREATE TABLE T as SELECT rownum ID, a.* from Dba_objects A;
Table has been created.
Sql> CREATE TABLE T1 as
2 SELECT rownum ID, OWNER, TABLE_NAME, CAST (' TABLE ' as VARCHAR2 ()) object_type
3 from Dba_tables;
Table has been created.
Sql> MERGE into T1 USING T
2 on (T.owner = T1. OWNER and t.object_name = T1. table_name and T.object_type = T1. object_type)
3 when matched THEN UPDATE SET t1.id = t.id
4 when not matched THEN INSERT VALUES (t.id, T.owner, T.object_name, T.object_type);
6165 lines have been merged.
Sql> SELECT ID, OWNER, object_name, object_type from T
2 minus
3 SELECT * from T1;
No rows selected
The merge syntax is actually very simple, and here's a little bit of an example.
sql> DROP TABLE T;
The table has been discarded.
sql> DROP TABLE T1;
The table has been discarded.
Sql> CREATE TABLE T as SELECT rownum ID, a.* from Dba_objects A;
Table has been created.
Sql> CREATE TABLE T1 as SELECT rownum ID, OWNER, table_name from Dba_tables;
Table has been created.
Sql> MERGE into T1 USING T
2 on (T.owner = T1. OWNER and t.object_name = T1. TABLE_NAME)
3 when matched THEN UPDATE SET t1.id = t.id
4 when not matched THEN INSERT VALUES (t.id, T.owner, t.object_name);
MERGE into T1 USING T
*
ERROR is on line 1th:
ORA-30926: Unable to get a stable set of rows in the source table
This error is the most common error in using the merge, which is caused by the fact that the record of T is not unique through the join condition. The simplest solution is similar:
Sql> MERGE into T1
2 USING (SELECT OWNER, object_name, MAX (ID) ID from T GROUP by OWNER, object_name) t
3 on (T.owner = T1. OWNER and t.object_name = T1. TABLE_NAME)
4 when matched THEN UPDATE SET t1.id = t.id
5 When not matched THEN INSERT VALUES (t.id, T.owner, t.object_name);
5775 lines have been merged.
In addition, the update of the merge statement cannot modify the columns used for the connection, otherwise it will be an error, as the details can refer to: http://blog.itpub.net/post/468/14844

===============================================================

ref:http://tomszrp.itpub.net/post/11835/263865

Prior to Oracle 10g, the merge statement supported 2 simple usages of matching updates and mismatched inserts, and Oracle enhanced the merge statement in 10g, adding conditional options and delete operations. Below I have a demo to briefly introduce the enhanced 10g merge and 10g before the use of the merge.

　

Referring to Oracle's SQL Reference, you can see the syntax for the merge statement as follows:
MERGE [hint] into [schema.] table [T_alias] USING [schema.]
{Table | view | subquery} [T_alias] On (condition)
When matched THEN Merge_update_clause
When not matched THEN merge_insert_clause;

Below I do a test on the Windows XP 10.2.0.1 version to see

Sql> select * from V$version; BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-prod pl/sql release 10.2.0.1.0-production CORE 10.2.0.1.0 Production TNS for 32-bit Windows : Version 10.2.0.1.0-production Nlsrtl version 10.2.0.1.0-production sql> One, creating a test table sql> CREATE table subs (MSI

D Number (9), 2 Ms_type char (1), 3 AreaCode number (3) 4);

Table has been created. Sql> CREATE TABLE Acct (MSID number (9), 2 Bill_month number (6), 3 AreaCode N

Umber (3), 4 fee number (8,2) default 0.00);

Table has been created.

Sql> sql> INSERT INTO subs values (905310001,0,531);

1 lines have been created.

Sql> INSERT INTO subs values (905320001,1,532);

1 lines have been created.

Sql> INSERT INTO subs values (905330001,2,533);

1 lines have been created.

Sql> commit;

Submit completed. Sql> two, the following first demonstrates the basic function of the merge 1 matched and not matched clausesUse the merge into Acct a using subs B on (A.MSID=B.MSID) while matched then update set A.AREACODE=B.AREAC
Ode when not matched then insert (Msid,bill_month,areacode) VALUES (b.msid, ' 200702 ', b.areacode);
        2 only the not matched clause, that is, insert only not update merge into Acct a using subs B on (A.MSID=B.MSID) when not matched

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 upd Ate set A.areacode=b.areacode Connected to Oracle Database 10g Enterprise Edition release 10.2.0.1.0 Connected A

      S study sql> SELECT * from subs;            Msid ms_type areacode-------------------------905310001 0 531 905320001 1 532 905330001 2

      Sql> SELECT * from Acct; Msid bill_month areacode FEE--------------------------------------sql> sql> mErge into Acct a 2 using subs B in (A.MSID=B.MSID) 3 when matched then 4 update set A.AREACODE=B. AreaCode 5 When not matched then 6 insert (Msid,bill_month,areacode) 7 values (B.msid, ' 200702 ',

B.areacode);

      Done sql> SELECT * from Acct; Msid bill_month areacode FEE--------------------------------------905320001 200702 532 0.00 9 05330001 200702 0.00 905310001 200702 531 0.00 sql> INSERT INTO subs values (9053400

01,3,534);

      1 row inserted sql> select * from subs;            Msid ms_type areacode-------------------------905340001 3 534 905310001 0 531 905320001 1     532 905330001 2 sql> sql> Merge into Acct a 2 using subs B on (A.MSID=B.MSID) 3

When not matched then 4 insert (Msid,bill_month,areacode) 5 values (B.msid, ' 200702 ', b.areacode); Done sql> SELECT * FROM Acct Msid bill_month areacode FEE--------------------------------------905320001 200702 532 0.00 9 05330001 200702 533 0.00 905310001 200702 531 0.00 905340001 200702 534-0.0

0 sql> Update subs set areacode=999;

      4 rows Updated sql> select * from subs;            Msid ms_type areacode-------------------------905340001 3 999 905310001 0 999 905320001 1

      999 905330001 2 999 sql> SELECT * from Acct; Msid bill_month areacode FEE--------------------------------------905320001 200702 532 0.00 9 05330001 200702 533 0.00 905310001 200702 531 0.00 905340001 200702 534-0.0 0 sql> sql> Merge into Acct a 2 using subs B in (A.MSID=B.MSID) 3 when matched then 4 upd

Ate set A.areacode=b.areacode;

      Done sql> SELECT * from Acct; Msid Bill_moNth AreaCode FEE--------------------------------------905320001 200702 999 0.00 905330001 
　
200702 999 0.00 905310001 200702 999 0.00 905340001 200702 999 0.00 sql>     
   Three, 10g enhanced one: condition operation 1 matched and not matched clauses simultaneous use of 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 if not matched then Inse
RT (Msid,bill_month,areacode) VALUES (b.msid, ' 200702 ', b.areacode) where b.ms_type=0;
        2 only the not matched clause, that is, insert only not update merge into Acct a using subs B on (A.MSID=B.MSID) when not matched

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 upd Ate set a.areacode=b.areacode where b.ms_type=0;
        
        
Connected to Oracle Database 10g Enterprise Edition release 10.2.0.1.0 Connected as study sel

      ECT * from subs;            Msid ms_type areacode-------------------------905310001 0 531 905320001 1 532 905330001 2

      Sql> SELECT * from Acct;       Msid bill_month areacode FEE--------------------------------------sql> sql> Merge into Acct a 2 Using subs B On (A.MSID=B.MSID) 3 when matched then 4 update set A.areacode=b.areacode 5 whe Re b.ms_type=0 6 when not matched then 7 insert (Msid,bill_month,areacode) 8 values (B.msid, ' 20

0702 ', B.areacode) 9 where b.ms_type=0;

      Done sql> SELECT * from Acct; Msid bill_month areacode FEE--------------------------------------905310001 200702 531 0.00 S

Ql> INSERT INTO subs values (905360001,0,536);

      1 row inserted sql> select * from subs; MsiD ms_type areacode-------------------------905360001 0 536 905310001 0 531 905320001 1     532 905330001 2 sql> sql> Merge into Acct a 2 using subs B on (A.MSID=B.MSID) 3          When not matched then 4 insert (Msid,bill_month,areacode) 5 values (B.msid, ' 200702 ', B.areacode) 6

where b.ms_type=0;

      Done sql> SELECT * from Acct; Msid bill_month areacode FEE--------------------------------------905310001 200702 531 0.00 9

05360001 200702 536 0.00 sql> Update Subs set areacode=888 where ms_type=0;

      2 rows Updated sql> select * from subs;            Msid ms_type areacode-------------------------905360001 0 888 905310001 0 888 905320001 1

      532 905330001 2 sql> select * from Acct; Msid bill_month areacode FEE--------------------------------------905310001 2007531 0.00 905360001 200702 536 0.00 sql> sql> Merge into Acct a 2 using Subs b On (A.MSID=B.MSID) 3 matched then 4 update set A.areacode=b.areacode 5 where b.ms_type=

0;

      Done sql> SELECT * from Acct; Msid bill_month areacode FEE--------------------------------------905310001 200702 888 0.00 9 05360001 200702 888 0.00 sql> Four, 10g enhanced two: delete operation an optional delete WHERE clause can is used to clean up a fter a merge operation.

   Only those rows which match both the in clause and the DELETE WHERE clause are.        
        Merge into Acct a using subs B in (A.MSID=B.MSID) when matched then update set A.areacode=b.areacode             

Delete where (b.ms_type!=0);

      Sql> select * from subs;            Msid ms_type areacode-------------------------905310001 0 531 905320001 1 532 905330001 2 SQ.L> SELECT * from Acct;            Msid ms_type areacode-------------------------905310001 0 531 905320001 1 532 905330001 2          sql> sql> Merge into Acct a 2 using subs B to (A.MSID=B.MSID) 3 when matched then 4

Update set A.areacode=b.areacode 5 delete where (b.ms_type!=0);

      Done sql> SELECT * from Acct; Msid ms_type areacode-------------------------905310001 0 531 sql> More detailed syntax, refer to the Oracle SQL reference manual
 。

T1 must be a table that actually exists for Oracle.

Declare
--T1 must be a table or view that is actually present in Oracle.
--create table T (C1 number,c2 number);
--insert into T values (1,2);
Type Type_rec_result is record (
C1 number,
C2 number);
Type Type_tab_result is table of Type_rec_result index by Binary_integer;
T1 Type_tab_result;
Begin
Merge into T1
Using T
On (T1. C1 = t.c1)
When matched then
Update
Set T1. C2 = T.c2
When not matched then
Insert
Values
(T.c1, T.C2);
End
/
pl/sql:ora-00942: Table or view does not exist