Merge into merged information

Syntax: (where as can be omitted)

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

Action: Updates or inserts the source data (from the actual table, view, subquery) into the specified table (which must actually exist) and relies on the on condition, which has the advantage of avoiding multiple insert and update operations. The merge is a targeted operator and does not allow 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. Examples are as follows:

drop table t;
CREATE TABLE T as SELECT rownum ID, a.* from Dba_objects A;
drop table T1;
CREATE TABLE T1 as
SELECT rownum ID, OWNER, TABLE_NAME, CAST (' TABLE ' as VARCHAR2 ()) object_type
From Dba_tables;
SELECT * from Dba_objects;
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
Insert all columns after not writing

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 mistakes,
The join condition does 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 make the condition that the condition must have parentheses
When matched then
Update Set A.areacode=b.areacode--When matching, perform update operations, and direct update syntax
No, you don't need to make a table name
When not matched then-When does not match, does insert operation, also does not need to make the table name, if specifies the field inserts, after inserts in parentheses, does not specify is inserts all
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 when 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;