Use of merge into in Oracle

This command uses a single statement to complete updating and inserting data from one or more data sources to the table. Oracle 9i, using this command must specify both the update and insert keywords, and ORACLE 10g makes the following changes.

1,insert and UPDATE are optional 2,update and insert can be followed by WHERE clause 3, in the on condition you can use constants to insert all rows into the target table, do not need to connect to the source table and the target table after the 4,update clause can be followed by the delete To get rid of some unwanted rows.

Example:

2. Create Table Products
4. (
6. product_id INTEGER,
8. Product_Name VARCHAR2 (60),
10. CATEGORY VARCHAR2 (60)
12. );
16. insert into products values (1501, ' VIVITAR 35MM ', ' Electrncs ');
18. insert into products values (1502, ' OLYMPUS IS50 ', ' Electrncs '   );
20. insert into products values (GYM, ' PLAY ', ' TOYS ');
22. insert into products values (1601, ' Lamaze ', ' TOYS ');
24. insert into products values (1666, ' HARRY POTTER ', ' DVD ');
26. commit;
30. Create table newproducts
32. (
34. product_id INTEGER,
36. Product_Name VARCHAR2 (60),
38. CATEGORY VARCHAR2 (60)
40. );
44. insert into newproducts values (1502, ' OLYMPUS CAMERA ', ' Electrncs ');
46. insert into newproducts values (1601, ' Lamaze ', ' TOYS ');
48. insert into newproducts values (1666, ' HARRY POTTER ', ' TOYS ');
50. insert into newproducts values (1700, ' WAIT INTERFACE ', ' BOOKS   ‘);
52. commit;
56. 1, can omit the update or insert
58. MERGE into Products p
60. 2 USING newproducts NP
62. 3 on (p.product_id = np.product_id)
64. 4 when matched Then
66. 5 UPDATE
68. 6 SET p.product_name = Np.product_name,
70. 7 p.category = np.category;

Use the Product_Name and Category fields in table NewProducts to update the Product_Name and category of the same product_id in the table products.

2. Insert the data from the NewProducts table into the table products when the condition is not satisfied.

1. MERGE into Products p
2. USING NewProducts NP
3. On (p.product_id = np.product_id)
4. When isn't matched Then
5. INSERT
6. VALUES (np.product_id, Np.product_name,
7. Np.category);

3, conditional Insert and update

1. 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;

Insert and update with WHERE clause

2. MERGE into Products p
4. USING NewProducts NP
6. On (p.product_id = np.product_id)
8. When matched Then
10. UPDATE
12. SET p.product_name = Np.product_name,
14. P.category = Np.category
16. WHERE p.category = ' DVD '
18. When isn't matched Then
20. INSERT
22. VALUES (np.product_id, Np.product_name, Np.category)
24. WHERE np.category! = ' BOOKS '

4, unconditional insert

1. MERGE into Products p
2. USING NewProducts NP
3. On (1=0)
4. When isn't matched Then
5. INSERT
6. VALUES (np.product_id, Np.product_name, Np.category)
7. WHERE np.category = ' BOOKS '
   

5,delete clause

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

SELECT *

from the products;

product_id Product_Name CATEGORY
--------------------------------------- -------------------- --------------------
1502 Macle22 Macle2_cate
1503 Macle3 Macle2_cate
1504 Macle Macle1_cate
1505 Macle5 Macle5_cate

The macle1_cate in 1504 satisfies the delete where, but does not satisfy the condition in on, so it has not been deleted.!!!!!! Focus

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Motivation:

You want to do insert/update directly with an SQL statement in Oracle.

Description

When writing SQL statements, we often encounter a large number of simultaneous insert/update statements, that is, when there is a record, it is updated (update), when no data exists, insert (insert).

Actual combat:

Next we have a task, there is a table T, there are two fields, a, B, we want to do a insert/update in table T, and if it does, update the value in T, or insert a record if it does not exist. In Microsoft's SQL syntax, a simple sentence can be judged, and the syntax in SQL Server is as follows:

if exists (select 1 from T where t.a= ' 1001 ') Update T is set t.b=2 where t.a= ' 1001 ' else insert into T (A, B) values (' 1001 ', 2);

The above statement indicates that if there is a record of a= ' 1001 ' in the T table, the value of B is set to 2, otherwise insert a a= ' ", b=2 record to T.

But then there is trouble in Oracle, remember that after Oracle 9i there is a merge into statement can be both insert and update, merge syntax is as follows:

MERGE into table_name ALIAS1
USING (table|view|sub_query) alias2
On (Join condition)
When matched then
UPDATE table_name
SET col1 = Col_val1,
col2 = Col2_val
When isn't matched then
INSERT (column_list) VALUES (column_values);

The above syntax should be easy to understand, then we follow the above logic to write again.

MERGE into T T1
USING (SELECT B, from T WHERE t.a= ' 1001 ') T2
On (t1.a=t2.a)
When matched then
UPDATE SET t1.b = 2
When isn't matched then
INSERT (A, b) VALUES (' 1001 ', 2);

The above statement seems to be right, actually, the statement can only be updated, but not insert, where is the error?

In fact, in Oracle, the merge statement was originally used for the update of the whole table, that is, ETL tools more commonly used syntax, the focus is on using.

To interpret the merge syntax in Chinese is:

Select the data in the ALIAS2, each of which is compared to the ALIAS1 on (join condition), if the match, the Update operation (update), if not match, insert operation (insert).

So, strictly speaking,"in a merge statement that has both INSERT and update syntax, the total number of insert/update records is the number of records ALIAS2 in the using statement. "

The above sentence is also very good explanation of the above statement why only update, but not insert, because all of the select is not the data, how can insert it:)

The next step is to change to the correct statement, which is much easier, as follows:

MERGE into T T1
USING ( SELECT ' 1001 ' as a,2 as B from dual)T2
On (t1.a=t2.a)
When matched then
UPDATE SET t1.b = t2.b
When isn't matched then
INSERT (A, B) VALUES (t2.a,t2.b);

Query results, ok!

Attention:

If you do not understand the principle of the merge statement, the merge statement is a more dangerous statement, especially when you only want to update a record, because inadvertently, you may have the entire table of data updated all over again .... Sweat!!!

One of the mistakes I've made is as follows, do you see what the problem is?

MERGE into T T1
USING (SELECT Count (*) CNT from T WHERE t.a= ' 1001 ') T2
On (t2.cnt>0)
When matched then
UPDATE SET t1.b = t2.b
When isn't matched then
INSERT (A, B) VALUES (t2.a,t2.b);

Use of merge into in Oracle