Oracle Merge into Usage

The merge in Oracle 10g has some of the following improvements:

1. Update or INSERT clauses are optional

2. Update and INSERT clauses can be added to the WHERE clause

3. The on condition uses the constant filter predicate to insert all rows into the target table without the need to connect the source and target tables

4, the UPDATE clause can be followed by a delete clause to remove some unwanted rows

Oracle9i introduces the merge command, which allows you to perform both inserts and updates 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.

First create the sample table:

Create TableProducts (product_idINTEGER, Product_NameVARCHAR2( -), CATEGORYVARCHAR2( -));DELETE  fromProducts ;Insert  intoProductsValues(1501,'VIVITAR 35MM','Electrncs');Insert  intoProductsValues(1502,'OLYMPUS IS50','Electrncs');Insert  intoProductsValues( the,'PLAY GYM','TOYS');Insert  intoProductsValues(1601,'Lamaze','TOYS');Insert  intoProductsValues(1666,'HARRY POTTER','DVD');Commit; Create Tablenewproducts (product_idINTEGER, Product_NameVARCHAR2( -), CATEGORYVARCHAR2( -));Insert  intoNewProductsValues(1502,'OLYMPUS CAMERA','Electrncs');Insert  intoNewProductsValues(1601,'Lamaze','TOYS');Insert  intoNewProductsValues(1666,'HARRY POTTER','TOYS');Insert  intoNewProductsValues(1700,'WAIT INTERFACE','BOOKS');Commit; SELECT *  fromProducts ;SELECT *  fromNewProducts;

1. An UPDATE or INSERT clause that can be omitted

In Oracle 9i, the merge statement requires that you specify both an INSERT and an UPDATE clause. In Oracle 10g, you can omit one of the update or INSERT clauses. The following example updates the information for the table products based on whether the product_id field of the table NewProducts matches:

MERGE intoProducts pusing newproducts NP on(p.product_id=NP. PRODUCT_ID) whenMatched Then  UPDATE SETP.product_name=Np. Product_Name, P.category=NP. CATEGORY;ROLLBACK; MERGE intoProducts pusing newproducts NP on(p.product_id=NP. PRODUCT_ID) when  notMatched Then  INSERT VALUES(NP. product_id, NP. Product_Name, NP. CATEGORY);SELECT *  fromProducts ;ROLLBACK;

2. Updates and inserts clauses with conditions

You can add a WHERE clause to the UPDATE or INSERT clause to skip the processing of some rows by the update or insert operation. The following example updates the table products data according to table NewProducts, but must also match the field category:

 into Products pusing newproducts NP  on = NP. product_id)when then  UPDATE     SET=  NP. Product_Name   WHERE=  NP. CATEGORY; ROLLBACK;

3. Unconditional Inserts

You can insert the data from the source table into the target table without connecting the source and target tables. This is useful when you want to insert all rows into the target table. Oracle 10g now supports the use of constant filter predicates in on conditions. Take a constant filter predicate example on (1=0). The following example inserts rows from the source table to the table products, without checking whether the rows exist in the table products:

 into Products pusing newproducts NP  on (1=0)  when  not  then  INSERT VALUES (NP. product_id, NP. Product_Name, NP. CATEGORY);

4. The newly added delete clause

The merge in Oracle 10g provides the option to clear rows when performing data operations. You can include the DELETE clause in the when matched and then update clause. The DELETE clause must have a WHERE condition to delete rows that match certain conditions. Rows that match the delete where condition but do not match the on condition are not removed from the table.

The following example validates the DELETE clause. We merge rows from table NewProducts to table products, but delete rows with category Electrncs.

MERGE intoProducts pusing newproducts NP on(p.product_id=NP. PRODUCT_ID) whenMatched Then  UPDATE     SETP.product_name=Np. Product_Name, P.category=Np. CATEGORYDELETE   WHERE(p.category= 'Electrncs') when  notMatched Then  INSERT VALUES(NP. product_id, NP. Product_Name, NP. CATEGORY);SELECT *  fromProducts ;ROLLBACK;

Oracle Merge into Usage