Oracle-merge usage

Oracle9i introduces the merge command. You can execute the inserts and updates operations on a table in an SQL statement at the same time. the merge command selects rows from one or more data sources to update or inserting to one or more tables. in Oracle 10g, merge has the following improvements:

1. The update or insert clause is optional.

2. You can add a where clause to the update and insert clauses.

3. Use the constant filter predicate in the on condition to insert all rows to the target table. You do not need to connect the source table to the target table.

4. The update clause can be followed by the delete clause to remove unnecessary rows.

First, create an example table:

create table PRODUCTS ( PRODUCT_ID INTEGER, PRODUCT_NAME VARCHAR2(60), CATEGORY VARCHAR2(60) ); 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(60), CATEGORY VARCHAR2(60) ); 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;

1. The update or insert clause that can be omitted

In Oracle 9i, the merge statement requires you to specify both the insert and update clauses. in Oracle 10 Gb, You can omit one of the update or insert clauses. the following example shows how to update the products information of the table based on whether the product_id field of the table newproducts matches:

SQL> MERGE INTO 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 p.category = np.category; 3 rows merged. SQL> SELECT * FROM products; PRODUCT_ID PRODUCT_NAME CATEGORY ---------- -------------------- ---------- 1501 VIVITAR 35MM ELECTRNCS 1502 OLYMPUS CAMERA ELECTRNCS 1600 PLAY GYM TOYS 1601 LAMAZE TOYS 1666 HARRY POTTER TOYS SQL> SQL> ROLLBACK; Rollback complete. SQL>

In the preceding example, the merge statement affects the rows whose product IDs are 1502,160 1 and 1666. their product names and classes are updated to values in the newproducts table. in the following example, the update clause is omitted and the new product_id In the table newproducts is inserted into the table products. data that can match the product_id in the two tables is not processed. in this example, you can see that the row with product_id = 1700 is inserted into the table products.

SQL> MERGE INTO products p 2 USING newproducts np 3 ON (p.product_id = np.product_id) 4 WHEN NOT MATCHED THEN 5 INSERT 6 VALUES (np.product_id, np.product_name, 7 np.category); 1 row merged. SQL> SELECT * FROM products; PRODUCT_ID PRODUCT_NAME CATEGORY ---------- -------------------- ---------- 1501 VIVITAR 35MM ELECTRNCS 1502 OLYMPUS IS50 ELECTRNCS 1600 PLAY GYM TOYS 1601 LAMAZE TOYS 1666 HARRY POTTER DVD 1700 WAIT INTERFACE BOOKS

2. Conditional updates and inserts clauses

You can add the WHERE clause to the update or insert clause to skip the update or insert operation. the following example updates the table's products data based on the table newproducts, but the field category must be matched at the same time:

SQL> MERGE INTO 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 WHERE p.category = np.category; 2 rows merged. SQL> SELECT * FROM products; PRODUCT_ID PRODUCT_NAME CATEGORY ---------- -------------------- ---------- 1501 VIVITAR 35MM ELECTRNCS 1502 OLYMPUS CAMERA ELECTRNCS 1600 PLAY GYM TOYS 1601 LAMAZE TOYS 1666 HARRY POTTER DVD SQL> SQL> rollback;

In this example, the product IDs are 1666, 1666, and match the on condition, but the category of does not match. therefore, the merge command only updates two rows of data. the following example shows how to use the WHERE clause in both updates and inserts clauses:

SQL> MERGE INTO 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 p.category = np.category 8 WHERE p.category = 'DVD' 9 WHEN NOT MATCHED THEN 10 INSERT 11 VALUES (np.product_id, np.product_name, np.category) 12 WHERE np.category != 'BOOKS' SQL> / 1 row merged. SQL> SELECT * FROM products; PRODUCT_ID PRODUCT_NAME CATEGORY ---------- -------------------- ---------- 1501 VIVITAR 35MM ELECTRNCS 1502 OLYMPUS IS50 ELECTRNCS 1600 PLAY GYM TOYS 1601 LAMAZE TOYS 1666 HARRY POTTER TOYS SQL>

Note that because the WHERE clause insert does not insert all rows that do not match the on condition to the table products.

3. Unconditional Inserts

You can insert data from the source table to the target table without connecting the source table to the target table. this is useful when you want to insert all rows into the target table. oracle 10 Gb now supports constant filter predicates in the on condition. for example, on (1 = 0 ). the following example inserts rows from the source table to the table products without checking whether these rows exist in the table products:

SQL> MERGE INTO products p 2 USING newproducts np 3 ON (1=0) 4 WHEN NOT MATCHED THEN 5 INSERT 6 VALUES (np.product_id, np.product_name, np.category) 7 WHERE np.category = 'BOOKS' SQL> / 1 row merged. SQL> SELECT * FROM products; PRODUCT_ID PRODUCT_NAME CATEGORY ---------- -------------------- ---------- 1501 VIVITAR 35MM ELECTRNCS 1502 OLYMPUS IS50 ELECTRNCS 1600 PLAY GYM TOYS 1601 LAMAZE TOYS 1666 HARRY POTTER DVD 1700 WAIT INTERFACE BOOKS 6 rows selected. SQL>

4. Newly Added Delete clause

Merge in Oracle 10 Gb provides the option to clear rows when performing data operations. you can include the delete clause in the when matched 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 deleted from the table.

In the following example, the delete clause is verified. We merge data from the table newproducts to the table products, but delete the rows whose category is into NCS.

SQL> MERGE INTO 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 p.category = np.category 8 DELETE WHERE (p.category = 'ELECTRNCS') 9 WHEN NOT MATCHED THEN 10 INSERT 11 VALUES (np.product_id, np.product_name, np.category) SQL> / 4 rows merged. SQL> SELECT * FROM products; PRODUCT_ID PRODUCT_NAME CATEGORY ---------- -------------------- ---------- 1501 VIVITAR 35MM ELECTRNCS 1600 PLAY GYM TOYS 1601 LAMAZE TOYS 1666 HARRY POTTER TOYS 1700 WAIT INTERFACE BOOKS SQL>

The row with the product ID 1502 is deleted from the table products because it matches both the on condition and the Delete where condition. the row with the product ID 1501 matches the Delete where condition but does not match the on condition, so it is not deleted. the row with the product ID 1700 does not match the on condition, so the table products is inserted. rows with the product ID 1601 and 1666 match the on condition but do not match the Delete where condition, so they are updated as values in the newproducts table.