Oracle's Merge____oracle

Turn from: http://xo-tobacoo.iteye.com/blog/182791

Summarized below. Using the merge is much faster than the traditional judgment before choosing to insert or update. 1) Main function
Provides conditional updating and inserting of data into a database table
If the row exists, an update operation is performed, and if it is a new row, an insert operation is performed
-Avoid a separate update
-Improve performance and ease of use
-Useful in data warehousing applications

2 The syntax of the merge statement is as follows:

MERGE [hint] into [schema.] table [T_alias] USING [schema.]

{Table | view | subquery} [T_alias] On (condition) when matched THEN merge_update_clause if not matched THEN

;

Just look at the example and know what's going on:

MERGE into Copy_emp c   
USING employees E on   
(c.employee_id=e.employee_id) when   
matched THEN  
UPDATE set< C4/>c.first_name=e.first_name,   
c.last_name=e.last_name,   
c.department_id=e.department_id when not   
Matched THEN  
INSERT VALUES (e.employee_id,e.first_name,e.last_name,   
e.email,e.phone_number,e.hire_date, e.job_id,   
e.salary,e.commission_pct,e.manager_id,   
e.departmetn_id);  

MERGE into Copy_emp c
USING employees E on
(c.employee_id=e.employee_id) when
matched THEN
UPDATE SET
C.first_name=e.first_name,
c.last_name=e.last_name,
c.department_id=e.department_id
When not matched THEN
INSERT VALUES (e.employee_id,e.first_name,e.last_name,
E.email,e.phone_number, e.hire_date,e.job_id,
e.salary,e.commission_pct,e.manager_id,

3 The use of the merge considerations:
To create a test table:

CREATE TABLE MM (ID number, NAME VARCHAR2 (20));

Inserting data

INSERT into MM VALUES (1, ' A ');

Execute:
MERGE into MN A
    USING MM B in
    (a.id=b.id) when
    matched THEN
    UPDATE SET a.id = b.id
    if not MAT Ched THEN
    INSERT VALUES (b.id, b.name);
On (a.id=b.id)
is an error because the field used on the ON clause is not available for update, that is, Oracle does not allow updates to the column modifications used for the connection
:
MERGE into MN A
    USING MM B On
    (a.id=b.id)
    When matched THEN
    UPDATE SET a.name = b.name
    as not matched THEN
    INSERT VALUES (b.id, b.name);
On (a.id=b.id)

reinsert: INSERT INTO MM VALUES (1, ' C ');
Re-execution:
MERGE into MN A
    USING MM B in
    (a.id=b.id) when
    matched THEN
    UPDATE SET a.name = B.name
    When not matched THEN
    INSERT VALUES (b.id, b.name);
On (a.id=b.id)

4 Update the data of the same table. You need to be aware of the details, because the use of the data set may be null, so the count () function is used.

MERGE into MN a
   using (select COUNT (*) Co from MN where mn.id=4) b
   on (b.co<>0)--Here you use count and <> What the. When
   matched THEN
      UPDATE
         SET a.name = ' E '
         where a.id=4 when not
   matched THEN
      INSERT