Merge into: insert/update operations using an SQL statement in Oracle

Motivation:

You want to use an SQL statement in Oracle to directly perform insert/update operations.

Note:

When writing SQL statements, we often encounter a large number of insert/update statements at the same time, that is, when there is a record, update (update), when there is no data, insert ).

Practice:

Next we will have a task with a table t with two fields A and B. We want to insert/update in Table T. If so, we will update the value of B in table t, if not, insert a record. In Microsoft's SQL syntax, you can simply make a judgment. The syntax in SQL Server is as follows:

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

The preceding statement indicates that if a = '20160301' record exists in table t, set the value of B to 2. Otherwise, insert a record a = '20160301 ', B = 2 records to T.

However, there is a problem in Oracle. Remember that there is a merge into statement after Oracle 9i that can be used for insert and update at the same time. The merge syntax is as follows:

MERGE INTO table_name alias1 USING (table|view|sub_query) alias2ON (join condition) WHEN MATCHED THEN     UPDATE table_name     SET col1 = col_val1,         col2     = col2_val WHEN NOT MATCHED THEN     INSERT (column_list) VALUES (column_values); 

The above syntax should be easy to understand, so we should write it again based on the above logic.

MERGE INTO T T1USING (SELECT a,b FROM T WHERE t.a='1001') T2ON ( T1.a=T2.a)WHEN MATCHED THEN  UPDATE SET T1.b = 2WHEN NOT MATCHED THEN   INSERT (a,b) VALUES('1001',2);

The preceding statement seems to be correct, right? In fact, this statement can only be updated, but cannot be inserted. Where is the error?

In fact, in Oracle, the merge statement was originally used to update the whole table, that is, the commonly used Syntax of ETL tools, with emphasis on using.

To explain the merge syntax in Chinese, it is:

Select data in alias2,Each entryBoth are compared with alias1 for on (join condition). If they match, update is performed. If they do not match, insert is performed ).

Therefore, strictly speaking,"In a merge statement with both insert and update syntax, the total number of insert/update records is the number of alias2 records in the using statement ."

The above statement explains why the statements written above can only be updated, but cannot be inserted. Because no data can be selected, how can we perform insert :)

It is much easier to change to the correct statement, as shown below:

MERGE INTO T T1USING (SELECT '1001' AS a,2 AS b FROM dual) T2ON ( T1.a=T2.a)WHEN MATCHED THEN  UPDATE SET T1.b = T2.bWHEN NOT MATCHED THEN   INSERT (a,b) VALUES(T2.a,T2.b);

Query Result, OK!

Note:

If you do not understand the principle of the merge statement, the merge statement is a dangerous statement, especially when you only want to update a record, you may have updated all the data in the entire table ..... khan !!!

One of the mistakes I have made is as follows. Do you see what the problem is?

MERGE INTO T T1USING (SELECT Count(*) cnt FROM T WHERE T.a='1001') T2ON (T2.cnt>0)WHEN MATCHED THEN  UPDATE SET T1.b = T2.bWHEN NOT MATCHED THEN   INSERT (a,b) VALUES(T2.a,T2.b);