[Oracle] Merge statement

The syntax for merge is as follows:

MERGE [hint] into [schema.] table [T_alias] USING [schema.] {Table | view | subquery} [T_alias] On (condition) when matched and merge_update_clause when not matched then merge_insert_clause;

What is merge and how is it used? Let's first look at a simple requirement:

The requirement is to update the data from the T1 table to the T2 table, and if the name of the T2 table already exists in the T1 table, add the money and, if it does not exist, insert the records of the T1 table into the T2 table.

You know, in the case of equivalence, must need at least two statements, one for update, one for the insert, and the statement must be with the logic of judgment, or write in the process, if it is a single statement, it is necessary to write the full condition,
Written in the update and INSERT statements, it is more cumbersome and error prone. If we understand the merge, we can implement the business logic directly with a single SQL without the help of stored procedures, and the code is concise, as follows:

MERGE into t2using t1on (t1.name=t2.name) when matched Thenupdateset T2. Money=t1. Money+t2. Moneywhen not matched Theninsertvalues (t1.name,t1. Money);

The four most flexible of the merge is the syntax and basic usage of merge, in fact the merge can be very flexible. 1.UPDATE and insert actions can only appear one (9I must appear at the same time!) )

--We can choose to update only the target table merge into T2using t1on (t1.name=t2.name) when matched Thenupdateset T2. Money=t1. Money+t2. money;--can also choose to insert only the target table without any update action merge into T2using t1on (t1.name=t2.name) when not matched Theninsertvalues (T1. Name,t1. Money);

2. You can add conditions to the merge statement

MERGE into t2using t1on (t1.name=t2.name) when matched Thenupdateset T2. Money=t1. Money+t2. Moneywhere t1.name= ' A ';

3. Use DELETE clause to clear rows

/* In this case, the first thing is to satisfy the t1.name=t2 first. The NAME of the record, if T2.name= ' A ' does not meet t1.name=t2. Name to filter out the recordset, then this delete will not take effect, under the conditions of the satisfied, you can delete the target table records. */merge into t2using t1on (t1.name=t2.name) when matched Thenupdateset T2. Money=t1. Money+t2. Moneydelete WHERE (t2.name = ' A ');

4. An unconditional insert can be used

/* method is very simple, after the syntax on the keyword written on the constant unequal conditions (such as 1=2), the matched statement insert becomes unconditional INSERT, as follows */merge into T2 USING T1 on (1=2) when not matched Then Insertvalues (t1.name,t1. Money);

The mistake of merge 1. Columns referenced by an ON clause cannot be updated

MERGE into t2using t1on (t1.name=t2.name) when matched Thenupdateset t2.name=t1.name;ora-38104: Cannot update the column referenced in the ON clause: "T2". " NAME "

2. The where order of the DELETE clause must last

MERGE into t2using t1on (t1.name=t2.name) when matched Thenupdateset T2. Money=t1. Money+t2. Moneydelete WHERE (t2.name = ' a ') where t1.name= ' a '; ora-00933:sql command does not end correctly

The 3.DELETE clause can only delete the target table and cannot delete the source table

/* Here you need to draw attention, regardless of whether the delete where (T2.name = ' A ') The T2 is rewritten as T1, the effect is the same, is to delete the target table! */select * from T1;name                      ------------------------------A                            10B                            20SELECT * from T2;name                      Money------------------------------A                            30C                            20MERGE to T2  USING T1 on  (t1.name=t2.name)  when Matched then  UPDATE  SET T2. Money=t1. Money+t2. Money  DELETE WHERE (t2.name = ' A ');    SELECT * from T1;name money                      ------------------------------A                            10B                            20SELECT * from T2;name                      Money------------------------------C                            20

4. To update the data for the same table, you need to worry about using the null value

SELECT * from T2;name------------------------------A 30C 20/* Requirements for self-updating of the T2 table, if a name=d record is found in the T2 table, the Money field of the record is updated to 100, and if the record of the Name=d does not exist, the record is automatically incremented, Name=d, and money=100. Complete the following code according to the syntax: */merge into t2using (select * from T2 where name= ' D ') TON (t.name=t2.name) when matched Thenupdateset T2. Money=100when not matched theninsertvalues (' D ', 200);--but the query found that the T-table should be added to the record because the Name=d does not exist, but actually there is no change at all.                            Sql> SELECT * from T2;name money-------------------------------------------------------A 30C 20/* Originally because this time select * from T2 where name= ' D ' is null, so there is a case that cannot be inserted, we can use The value of COUNT (*) will not be equivalent to an empty feature, as follows: */merge into t2using (select COUNT (*) CNT from T2 where name= ' D ') TON (t.cnt<>0) when MAT Ched Thenupdateset T2. Money=100when not matched theninsertvalues (' D ', 100);                    Sql> SELECT * from T2;name money-------------------------------A        30C 20D 100 

5. You must obtain a stable set of rows in the source table

---Construct the data, note that there is an ORA-30926 error insert into T1 VALUES (' a ', 30) when inserting an A record more. COMMIT;---at this point continue to execute the following merge into T2using t1on (t1.name=t2.name) when matched Thenupdateset T2. Money=t1. Money+t2. money;ora-30926: The merge statement in a stable set of row/*oracle in the source table should be guaranteed to be unique in the condition on, t1.name=t2. Name, the T1 table record corresponds to the two records of the T2 table, so there is an error. The solution is very simple, for example, we can T1 table and T2 table associated Word Jianjian main key, so it is basically impossible to have such a problem, and generally speaking, the merge statement of the associated fields with each other has a primary key, the merge efficiency will be higher! Or you can avoid such errors by making an aggregation of the ID column of the T1 table, which is merged into a single line. Example: */   MERGE into T2  USING (select Name,sum (Money) as money from T1 GROUP by NAME) T1 on  (t1.name=t2.name)  WH EN matched then  UPDATE