Use of merge into statements in Oracle

Motivation: You want to use an SQL statement in Oracle to perform Insert/Update operations directly. 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, update the value of B in T. If it does not exist, 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 = '000000' record exists in table T, set the value of B to 2. Otherwise, Insert a record with a = '000000' and B = 2 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: SQL code: 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 not matched then insert (column_list) VALUES (column_values); The above syntax should be easy to understand, so we should write it again according to the above logic. SQL code merge into t T1 USING (SELECT a, B FROM T WHERE t. a = '20160901') T2 ON (T1.a = T2.a) when matched then update set T1. B = 2 when not matched then insert (a, B) VALUES ('20160301', 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. The Merge syntax is interpreted in Chinese as follows: the Select data in alias2 is compared with alias1 in ON (join condition, update. If no match exists, Insert ). 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 the Insert statement?) The next step is to change the statement to the correct one. The following is the SQL code: merge into t T1 USING (SELECT '000000' 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 result, OK! 3 pieces by author