Oracle--merge

I. Overview

Use the merge declaration to filter records from one or more tables or views for updating or inserting into a table or view. You can specify criteria to decide whether to perform an update operation or an insert operation into the target table or view.

This declaration is a convenient way to combine multiple operations. This allows you to avoid a large number of DML operations such as Insert/update/delete.

The merge is a deterministic statement. You cannot update multiple times in the merge statement on the same target table.

MERGE[into [schema.] Table [T_alias] --Data set S (only table)USING[schema.]{table|View|subquery}[T_alias] --DataSet D (can be a table, subquery, view) on(condition) Association conditions for--s and D  whenMatched Thenmerge_update_clause data in--d to update the data records associated with S  when  notMatched ThenMerge_insert_clause;data inserted into the S dataset that do not satisfy the association condition in--d

Ii. examples

Here's an example of using the Scott mode table:

First create the table and insert the data

--Create a tableCreate Tableemp_dept (empno Number(4)Primary Key  not NULL, enamevarchar2(Ten), Deptno Number(2), Deptnamevarchar2( -));--Inserting DataInsert  intoemp_deptSelectE.empno,e.ename,d.deptno,d.dname fromEMP e,dept DwhereE.deptno=D.deptno andRowNum< 5;

Modify some ename in a table to make it different from the data in the EMP table

Compare the results of the emp_dept table and the Emp,dept table join query as follows:

Select *  from (Select  e.empno,e.ename,d.deptno,d.dname from emp e,dept dwhere  =  D.deptno) s, emp_dept edwhere ed.empno (+= s.empno;

Insert the results of the Emp,dept table join query into the emp_dept table using merge

Merge intoemp_dept D--First Data set: Table using of inserted data (SelectE.empno,e.ename,d.deptno,d.dname fromEMP e,dept DwhereE.deptno=D.deptno) s--second data set  on(D.empno=s.empno) Association of--D and S tables  whenMatched Then   Update  SetD.ename=S.ename when  notMatched Then   Insert(Empno,ename,deptno,deptname)Values(s.empno,s.ename,s.deptno,s.dname);Commit;-You must commit the transaction or you cannot see the data

The results of the federated query are as follows:

Oracle--merge