Merge_into Usage Detailed

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.

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. Updates and inserts clauses with conditions

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.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
Ten Inserts
VALUES (np.product_id, Np.product_name, Np.category)
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.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
Ten Inserts
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 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.