Merge into statement instead of Insert/update in Oracle application actual combat __oracle

Original: Http://blog.csdn.net/bruesz/archive/2007/11/20/1894836.aspx

motivation:

Want to use an SQL statement directly in Oracle to Insert/update operations.

Description:

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

actual combat:

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

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

The above statement shows 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= ' and b=2 ' record to T.

But then there's a problem with Oracle, remember that after Oracle 9i there is a merge into statement that can be insert and update at the same time, the syntax of the merge is as follows: merge  into   TABLE_NAME ALIAS1&NBSP
using  (table | view | sub_query)  alias2
on   (join  condition) &NBSP
when  MATCHED  THEN  
     UPDATE  table_name 
 & nbsp;   SET  col1  =  col_val1, 
         col2      =  col2_val 
When   not  MATCHED  THEN  
     INSERT   (column_list)   VALUES   (column_values); 

The above syntax everyone should be easy to understand, then we follow the logic of the above to write again. MERGE into T T1
USING (SELECT a,b from T WHERE t.a = ' 1001 ') T2
On (t1.a = t2.a)
When matched THEN
UPDATE SET t1.b = 2
When not matched THEN
INSERT (a,b) VALUES (' 1001 ', 2);

The above statement seems quite right, in fact, the statement can only be updated, and cannot be insert, where is the error.

In fact, in Oracle, the merge statement was originally used to update the whole table, which is the common syntax of the ETL tool, with the emphasis on using.

To explain the merge syntax in Chinese is:

The data that is select in the Alias2 is compared to the ALIAS1 on (join condition), and if it matches, the update is performed (update), and if it does not match, the insert operation is made.

Thus, in a strict sense,"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 a good explanation of why the statement written above can only update, and can not be insert, because the Select data, how to insert it:

The next change to the correct statement 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 not matched THEN
INSERT (a,b) VALUES (t2.a,t2.b);

Query results, ok!