Merge usage in Oracle

The MERGE statement is a new syntax of Oracle9i, used to merge update and INSERT statements. The MERGE statement is used to query another table based on the connection conditions of one table or subquery. The join condition matching is updated, and the INSERT statement cannot be executed. This syntax only requires one full table scan to complete all the work, and the execution efficiency is higher than INSERT + UPDATE.

Syntax

MERGE [INTO [schema.] table [t_alias]

USING [schema.] {table | view | subquery} [t_alias]

ON (condition)

When matched then merge_update_clause

When not matched then merge_insert_clause;

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 123456', 'invalid NCS ');

Insert into PRODUCTS values (1502, 'olympus is50', 'using NCS ');

Insert into PRODUCTS values (1600, 'play gym', 'toys ');

Insert into PRODUCTS values (1601, 'lamaze', 'toys ');

Insert into PRODUCTS values (1666, 'Harry POTTER ', 'dve ');

Commit;

 

Create table NEWPRODUCTS

(

PRODUCT_ID INTEGER,

PRODUCT_NAME VARCHAR2 (60 ),

CATEGORY VARCHAR2 (60)

);

 

Insert into NEWPRODUCTS values (1502, 'olympus CAMERA ', 'using NCS ');

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 2 3 4 5 6 7 Next Page