Oracle Merge into usage detailed

1. MERGE into uses
MERGE into is a new feature that appears only after Oracle 9i. So what is this feature?
To put it simply, it is: "There are updates, none are inserted"
From this sentence, it should be understood that when merge into operates an object ' a ', there is another result set as the source data ' B '.
' Merge into ' compares the data in B with the data in a according to a certain condition ' C ', if the data in a meets the C condition, then the update operation, if the condition ' C ' is not met, insert operation. (Please note this correspondence)

2. Grammatical structure

MERGE [into] [schema.] Table [alias]
USING {[Schema.] Table|views|query} [alias]
on {condition}
When matched then UPDATE SET {clause}
When isn't matched then INSERT VALUES {clause}

We can use it for processing single data, or for batch processing of data. For merge into, it was Zhang Fei eating bean sprouts, a piece of cake. and efficiency is more efficient than performing update+insert operations alone.
Note, however, that result set B in the using statement cannot be the same as the merge into Object A, otherwise, because the result set, a, is identical.

When on () the equivalent judgment, can only do update operation, cannot insert operation, when on () do not equal judgment, can only insert operation, cannot do update operation.

First create the sample 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 (GYM, ' PLAY ', ' 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. An UPDATE or INSERT clause that can be omitted

Update use, omit insert:

MERGE into Products P
USING NewProducts NP
On (p.product_id = np.product_id)
When matched then
UPDATE
SET p.product_name = Np.product_name,
P.category = np.category;

Insert use, omit Update:MERGE into Products p
USING NewProducts NP
On (p.product_id = np.product_id)
When isn't matched then
INSERT
VALUES (np.product_id, np.product_name,np.category);

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 and updates it according to the criteria category:

MERGE into Products P
USING NewProducts NP
On (p.product_id = np.product_id)
When matched then
UPDATE
SET P.product_name = Np.product_name
WHERE p.category = np.category;

MERGE into Products P
USING NewProducts NP
On (p.product_id = np.product_id)
When matched then
UPDATE
SET p.product_name = Np.product_name,
P.category = Np.category
WHERE p.category = ' DVD '
When isn't matched then
INSERT
VALUES (np.product_id, Np.product_name, Np.category)
WHERE np.category! = ' BOOKS '

3. Two-table connection 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:

Sql> MERGE into Products p
USING NewProducts NP
On (1=0)
When isn't matched then
INSERT
VALUES (np.product_id, Np.product_name, Np.category)
WHERE np.category = ' BOOKS '

Oracle Merge into usage detailed