Introduction to mergeinto in Oracle

Blog. csdn. netyuzhicarticledetails1896878blog. csdn. netmacle2010articledetails5980965 this command uses a statement to update and insert data to a table from one or more data sources. in ORACLE9i, you must specify both the UPDATE and INSERT keywords when using this command.

Http://blog.csdn.net/yuzhic/article/details/1896878 http://blog.csdn.net/macle2010/article/details/5980965 the command uses a statement to UPDATE and INSERT data to a table from one or more data sources. ORACLE 9i, using this command must specify both the UPDATE and INSERT keywords, ORACLE

Http://blog.csdn.net/yuzhic/article/details/1896878

Http://blog.csdn.net/macle2010/article/details/5980965

This command uses a statement to update and insert data to a table from one or more data sources. in ORACLE 9i, the UPDATE and INSERT keywords must be specified at the same time. ORACLE 10g has made the following changes.

1. insert and update are optional. UPDATE and INSERT can be followed by the WHERE clause 3. In the ON condition, constants can be used to insert all rows to the target table, you do not need to connect to source table and target table 4. The UPDATE clause can be followed by delete to remove unnecessary rows.

Example:

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;

Update Data:

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;

Use the product_name and category fields in the table newproducts to update the product_name and category of the same product_id In the table products.

Implementation data differential insertion:

Merge into products p
Using newproducts np
On (p. product_id = np. product_id)
When not matched then
Insert values (np. product_id, np. product_name, np. category );

When the conditions are not met, INSERT the data in the newproducts table into the products table.

Conditional insert

Merge into products p
Using newproducts np
On (1 = 1)
When not matched then
Insert
Values (np. product_id, np. product_name, np. category)
Where np. category = 'f1'

Conditional insert and update

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 = 'F3'
When not matched then
Insert
Values (np. product_id, np. product_name, np. category)
Where np. category! = 'F4'

Unconditional insert:

Merge into products p
Using newproducts np
On (1 = 0)
When not matched then
Insert
Values (np. product_id, np. product_name, np. category)
Where np. category = 'f1 ';

Delete clause;

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
Delete where category = 'q1 ';

The where clause can be executed to delete data in the products table only when the delete operation meets the on condition.

Before using this syntax, be sure to perform a test.