Merge into statement instead of Insert/update application in Oracle

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);

Merge into statement instead of Insert/update application in Oracle