Use of Meger in Oracle

Original post address: http://blog.csdn.net/nsj820/article/details/5755685

Oracle9i introduces the merge command, which enables you to perform both inserts and updates operations on a table in one SQL statement. The merge command selects rows from one or more data sources to updating or inserting to one or more tables. The merge in Oracle 10g has the following improvements :

1. An UPDATE or INSERT clause is optional

2, UPDATE and INSERT clauses can be added to the WHERE clause

3. Use constant filter predicates to insert all rows into the target table in the on condition without the need to connect the source and destination tables

4, after the update clause can be followed by the DELETE clause to remove some unwanted rows

Syntax:

Mergehintintoschema. TABLE T_alias

Usingschema. {TABLE | VIEW | subquery} T_alias

On (CONDITION)

Whenmatchedthen Merge_update_clause

Whennotmatchedthen Merge_insert_clause/merge_delete_clause;

Association:
Mergeinto is a unique function, equivalent in the MSSQL

Ifexists (...)

Updatetable

Else

Insertinto table.

Mergeinto syntax is not only verbose, but also much more efficient than if exists, and is commonly used to synchronize database tables between Oracle.

Example:

1, create the test table and data

[C-sharp] View Plain copy print? drop table products;   drop table newproducts;   create table  products   (   product_id integer,   product_name varchar2),    CATEGORY VARCHAR2   );   insert into products values  ( 1501,  ' vivitar 35mm ',  ' Electrncs ');   insert into products values  (1502,  ' Olympus is50 ',  ' Electrncs ');   insert into products values   (1600,  ' play gym ',  ' TOYS ');   insert into products values  ( 1601,  ' Lamaze ',  ' TOYS ');   insert into products values  (1666,  ' Harry potter ',  ' DVD ');   commit;   create table newproducts   (    product_id integer,   product_name varchar2,   CATegory varchar2   );   insert into newproducts values  (1502,   ' Olympus camera ',  ' Electrncs ');   insert into newproducts values  (1601,  ' Lamaze ',  ' TOYS ');   insert into newproducts values  (1666,  ' Harry potter ',  ' TOYS ');   insert into newproducts values  (1700,  ' Wait interface ',  ' books ');   commit;  

2. Matching Update

[C-sharp] view plain copy print? MERGE into Products P USING newproducts np on (p.product_id = NP. PRODUCT_ID) when matched THEN UPDATE SET p.product_name = NP. Product_Name, p.category = NP.      CATEGORY;   SELECT * FROM Products; SELECT * from NewProducts;

3, do not match the insertion

[C-sharp] view plain copy print? MERGE into Products P USING newproducts np on (p.product_id = NP. PRODUCT_ID) When not matched THEN INSERT (product_id, Product_Name, CATEGORY) VALUES (NP . product_id, NP. Product_Name, NP.      CATEGORY);   SELECT * FROM Products; SELECT * from NewProducts;

4, matching with where/on update

[C-sharp] View Plain copy print? merge into products p   using newproducts np   ON  (P.PRODUCT_ ID = NP. product_id)    when matched then     update set p.product_name  = NP. PRODUCT_NAME WHERE P.CATEGORY = NP. category;      merge into products p   using newproducts  np   on  (P.PRODUCT_ID = NP. PRODUCT_ID AND P.CATEGORY = NP. CATEGORY)    when matched then     update set p.product_name  = NP. product_name;      select * from products;   SELECT * FROM  NEWPRODUCTS;      select *     FROM PRODUCTS A     INNER JOIN NEWPRODUCTS B       on a. product_id = b.product_id      AND A.CATEGORY = B.CATEGORY;   

5, matching with where to update, insert

[C-sharp] View Plain copy print? merge into products p   using newproducts np   ON  (P.PRODUCT_ ID = NP. product_id)    when matched then     UPDATE         SET P.PRODUCT_NAME = NP. product_name,            P.CATEGORY      = NP. category      WHERE P.CATEGORY =  ' DVD '    when not  matched then     INSERT        (product_id       ,PRODUCT_NAME       ,category)       values        (NP. product_id       ,NP. product_name       ,NP. CATEGORY)  where np. category !=  ' Books ';      select * from products;   select * from newproducts;   

6, on constant expression

[C-sharp] view plain copy print? MERGE into the products P USING newproducts NP on (1 = 0) While not matched THEN INSERT (product_id, PROD Uct_name, CATEGORY) VALUES (NP. product_id, NP. Product_Name, NP. CATEGORY) WHERE NP.      CATEGORY = ' books ';   SELECT * FROM Products; SELECT * from NewProducts;

7, matching Delete, mismatched insert

[C-sharp] View Plain copy print? merge into products p   using newproducts np   ON  (P.PRODUCT_ ID = NP. product_id)    when matched then     UPDATE         SET P.PRODUCT_NAME = NP. product_name,            P.CATEGORY      = NP. category delete      WHERE  (p.category =  ' Electrncs ')    when not matched then     INSERT        (PRODUCT _id       ,PRODUCT_NAME       ,category)      VALUES        (NP. product_id       ,NP. product_name       ,NP. CATEGORY);      Select *&nbsP from products;   select * from newproducts;  

8. The source table is a subquery (self join)

[C-sharp] View Plain copy print? merge into products p   using  (Select count (*)  CO FROM  Products where products. product_id = 1501)  B   on  (b.co <> 0)    when  matched then     UPDATE SET P.PRODUCT_NAME =  ' Kebo '  where  P.PRODUCT_ID = 1501   when not matched then      insert        (product_id       ,PRODUCT_NAME        ,category)      VALUES        ( 1501       , ' Kebo '        , ' NBA ';       merge into products p   using  (Select count (*)  CO FROM  Products where products. product_id = 1508)  b   on  (b.co <> 0)    when matched then      update set p.product_name =  ' Kebo '  WHERE P.PRODUCT_ID = 1508    when not matched then     INSERT        ( product_id       ,PRODUCT_NAME       ,category)      VALUES        (1508       , ' Kebo '        , ' NBA '];      select * from products ;  

Advantages:

-Avoid a separate update

-Improve performance and ease of use

-Useful in data warehousing applications

-Using the merge is much faster than the traditional first judgment before choosing to insert or update

places to look for:

1. From the grammatical conditions (on (join condition)), the merge into is also a dangerous syntax. If not used carefully, the source table will be all covered to the target table, both dangerous and wasteful efficiency, violating the principle of incremental synchronization. Therefore, in the design of the table structure, generally each record has the "Update Time" field, with the target table "Maximum update Time" to determine whether the source table data updates and new information. 2, the updated field, do not allow the associated conditions of the field (join condition). For example, if the condition is a.id=b.id, then using "SET a.id=b.id" will quote an inexplicable hint error.