The use of Merge...into grammar

The basic function 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

second, 10g enhanced one: conditional 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 when is 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
An optional DELETE WHERE clause can is used to a merge operation.
Only those rows which match both the in clause and the DELETE WHERE clause are.
For example:

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);

MERGE into table_name as Table_alias
USING (Table|view|sub_query) as Alias
On (Join condition)
When matched THEN
UPDATE SET
col1 = Col_val1,
col2 = Col2_val--9i can not have a where condition, 10g can
When not matched THEN
INSERT (column_list)-Multiple columns separated by commas//can not specify columns
VALUES (column_values); --9i can not have a where condition, 10g can

   Role: UPDATE or insert source data (from actual tables, views, subqueries) into the specified table (must actually exist),
    depends on condition, and the benefit is to avoid multiple inserts and update operations. The merge is a targeted operator that
    does not allow for the same row insert or update operation in one merge statement.
    This syntax only needs a full table scan to complete all the work, execution efficiency is higher than insert+update.
    For example:
    drop table t;
    CREATE table T as SELECT R Ownum ID, a.* from Dba_objects A;
    drop table T1;
    CREATE TABLE T1 as
    SELECT rownum ID, OWNER, TABLE_NAME, CAST (' TABLE ' as VAR CHAR2) object_type
    from dba_tables;
    select * from Dba_objects;
& nbsp;   select * from Dba_tables;

MERGE into T1 USING T
On (T.owner = T1. OWNER and t.object_name = T1. table_name and T.object_type = T1. object_type)
When matched THEN UPDATE SET t1.id = t.id
When not matched THEN INSERT VALUES (t.id, T.owner, T.object_name, T.object_type);

Insert all columns after--insert not write
MERGE into T1 USING T
On (T.owner = T1. OWNER and t.object_name = T1. TABLE_NAME)
When matched THEN UPDATE SET t1.id = t.id
When not matched THEN INSERT VALUES (t.id, T.owner, T.object_name, T.object_type);

-Common errors, the connection conditions do not get a stable row, you can use the following subquery

MERGE into T1
USING (SELECT OWNER, object_name, MAX (ID) ID from T GROUP by OWNER, object_name) t
On (T.owner = T1. OWNER and t.object_name = T1. TABLE_NAME)
When matched THEN UPDATE SET t1.id = t.id
When not matched THEN INSERT VALUES (t.id, T.owner, t.object_name);

SELECT ID, OWNER, object_name, object_type from T
Minus
SELECT * from T1;
drop table Subs;
CREATE TABLE Subs (Msid number (9),
Ms_type char (1),
AreaCode Number (3)
);
drop table acct;
CREATE TABLE Acct (MSID number (9),
Bill_month Number (6),
AreaCode Number (3),
Fee number (8,2) default 0.00);
INSERT into subs values (905310001,0,531);
INSERT into subs values (905320001,1,532);
INSERT into subs values (905330001,2,533);
Commit
Merge into Acct A--Table of operations
Using subs B On (A.MSID=B.MSID)--Use the table of the original data source, and set conditions that must have parentheses
When matched then
Update Set A.areacode=b.areacode

--When the match is performed, the update operation is not the same as the syntax for direct update, and there is no need to make a table name
When not matched then

--When do not match, the insert operation, do not need to make table name, if the specified field inserts, after the insert with parentheses marked, not specified is all inserted
Insert (Msid,bill_month,areacode) VALUES (b.msid, ' 200702 ', b.areacode);

-In addition, the update of the merge statement cannot modify the columns used for the connection, or it will error
SELECT * from Acct;
SELECT * from Subs;
①10g new feature, single action
Merge into Acct A
Using subs B on (A.MSID=B.MSID)
When not matched then--only a single not matched, only do inserts, do not update, only a single matched, only do update operations
Insert (A.msid,a.bill_month,a.areacode) VALUES (b.msid, ' 200702 ', b.areacode);
Update acct set areacode=800 where msid=905320001;
Delete from acct where areacode=533 or areacode=531;
INSERT into acct values (905320001, ' 200702 ', 800,0.00);
--Delete duplicate rows
Delete from subs B where b.rowid< (
Select Max (A.ROWID) from subs a where a.msid=b.msid and A.ms_type=b.ms_type and
A.areacode=b.areacode);
②10g new attribute, after the merge operation, only the matching update operation can be used to delete the row in the target table that satisfies the condition with the delete WHERE clause
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)
When not matched then
Insert (Msid,bill_month,areacode)
VALUES (B.msid, ' 200702 ', B.areacode)
where b.ms_type=0;
③10g new features to meet conditions for inserts and updates
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;
SELECT * from subs where ms_type=0;