Use of merge in Oracle DML statements

Copying data from one table to another, inserting new data, or replacing old data is a problem that every Oracle DBA will frequently encounter.

In the years before the oracle9i, we have to find out whether there is old data, if useful update replacement, or INSERT statement inserted, there are some tag variables and so on, cumbersome.

Now that oracle9i specifically provides the merge statement for this situation, making this work exceptionally easy, Oracle9i introduces the merge command, where you can perform 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