Oracle Merge into usage detailed __oracle

Oracle9i introduces the merge command, which enables you to perform both inserts and U on a table in an SQL statement PDA tes operation. The merge command selects rows from one or more data sources to upd ATI ng or inserting to one or more tables.
Oracle 10g Merge 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. On conditions use constant filter verbs to insert all rows into the target table without the need to connect the source table and the target table

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


First create the sample table:

CREATE TABLE Products ( product_id INTEGER, Product_Name VARCHAR2 (60), CATEGORY VARCHAR2 (60) ); Insert into the products values (1501, ' VIVITAR 35MM ', ' Electrncs '); Insert into the products values (1502, ' OLYMPUS IS50 ', ' Electrncs '); Insert into the products of values (1600, ' Play GYM ', ' TOYS '); Insert into the products values (1601, ' Lamaze ', ' TOYS '); Insert into the 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, can omit the update or INSERT clause

In Oracle 9i, the merge statement requires that you specify both the INSERT and UPDATE clauses. At 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:

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 example above, the merge statement affects rows that are product IDs 1502, 1601, and 1666. Their product names and types are updated to the values in table NewProducts. The following example omits the UPDATE clause, inserts a new product_id from the table newproducts into the table products, and does nothing to match the product_id data in the two tables. From this example you can see that product_id=1700 rows are 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, with the condition of U PDA tes and INSERTS clauses

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

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 ID is 1502,1601 and 1666 match on condition but 1666 category does not match. Therefore, the merge command updates only two rows of data. The following example shows the use of the WHERE clause in both the updates and inserts clauses:

Sql> MERGE into products p     2 USING newproducts NP     3 on (p.product_id = Np.pro duct_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     INSERT     VALUES (np.product_id, Np.product_name, np.category) &nbs P   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 Because there is a WHERE clause insert that does not insert all rows to table products that do not match the on condition.

3, the unconditional inserts

You can insert 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 to the target table. Oracle 10g now supports the use of constant filter predicates in the on condition. Give a constant filter predicate example on (1=0). The following example inserts rows to table products from the source table and does not check whether the 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, 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 THEN update clause. The DELETE clause must have a WHERE condition to remove 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.

The following example validates the DELETE clause. We merge rows from table newproducts into table products, but delete category as Electrncs rows.

sql> MERGE into products p     2 USING newproducts NP     3 on (p.pr oduct_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 = ' electrn CS '     9 when not matched THEN     Ten INSERT     One VALUES (np.product_id, Np.pro Duct_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 line with Product ID 1502 is deleted from the table products because it matches both the on condition and the delete where condition. A row with a product ID of 1501 matches the delete where condition but does not match the on condition, so it is not deleted. A row with a product ID of 1700 does not match the on condition, so it is inserted into the table products. Rows with product IDs 1601 and 1666 match the on condition but do not match the delete where condition, so they are updated to the values in table NewProducts.