The merge statement is used to combine update and INSERT statements, large data operations for a table, and additions and deletions to improve performance __c language

This command uses a statement to complete the update and insert data for a table from one or more data sources. Oracle 9i, the command must specify both update and insert keywords, and Oracle 10g makes the following changes.

1,insert and UPDATE are optional 2,update and inserts can be followed by a WHERE clause 3, in which you can use constants to insert all rows into the target table, and you do not need to connect to the source and destination table 4,update clauses to follow the delete To get rid of some unwanted rows.

Example:CreateTableProducts (product_idINTEGER, Product_Name VARCHAR2, CATEGORY VARCHAR2 (60));Insert intoProductsValues(1501, ' VIVITAR 35MM ', ' Electrncs ');Insert intoProductsValues(1502, ' OLYMPUS IS50 ', ' Electrncs ');Insert intoProductsValues(1600, ' Play GYM ', ' TOYS ');Insert intoProductsValues(1601, ' Lamaze ', ' TOYS ');Insert intoProductsValues(1666, ' HARRY POTTER ', ' DVD ');Commit;CreateTableNewProducts (product_idINTEGER, Product_Name VARCHAR2, CATEGORY VARCHAR2 (60));Insert intoNewProductsValues(1502, ' OLYMPUS CAMERA ', ' Electrncs ');Insert intoNewProductsValues(1601, ' Lamaze ', ' TOYS ');Insert intoNewProductsValues(1666, ' HARRY POTTER ', ' TOYS ');Insert intoNewProductsValues(1700, ' Wait INTERFACE ', ' books ');Commit;
1, can omit the update or insert MERGE intoProducts P 2 USING newproducts NP 3 on(p.product_id = np.product_id) 4 whenMatchedTHEN5UPDATE6SETP.product_name = np.product_name, 7 p.category = np.category;

Use the Product_Name and Category fields in table NewProducts to update the Product_Name and category of the same product_id in the table products.

2 Inserts the data from the NewProducts table into the table products when the condition is not satisfied. merge  into  products p        USING  NEWPRODUCTS NP          on   (p.product_id =  np.product_id)          when  not matched   THEN          INSERT           VALUES   (np.product_id, np.product_name,          np.category);   

3, with the conditional insert and update MERGE into the 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;

Insert and update all have where words

MERGE intoProducts P USING newproducts NP on(p.product_id = np.product_id) whenMatchedTHENUPDATESETP.product_name = np.product_name, p.category = np.categoryWHEREp.category = ' DVD ' whenNot matchedTHENINSERTVALUES(np.product_id, Np.product_name, Np.category)WHERENp.category!= ' books '

4, unconditional insert MERGE into the products p USING newproducts np on (1=0) as not matched       THEN      INSERT VALUES       (np.product_id, Np.product_name, Np.category) WHERE np.category = ' books '

5,delete clause

1 Merge into the products P
2 using NewProducts NP
3 on (p.product_id = np.product_id)
4 when matched then
5 Update
6 Set p.product_name = Np.product_name
7 Delete where category = ' macle1_cate ';