[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. Assuming that the name of the T2 table already exists in the T1 table, the money is accumulated, assuming it does not exist. Insert a record 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 insert, and the statement must be with the inferred logic, or write in the process, the assumption is a single statement, it is necessary to write the full condition.
Written in the update and INSERT statements, it is more cumbersome and easy to make mistakes. Assuming that 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 very concise. Details such as the following:

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 use of the merge, in fact, the merge can be very flexible. The 1.UPDATE and insert actions can occur only 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) and 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 such a case, the first thing is to satisfy the t1.name=t2 first. NAME of the record, assuming that t2.name= ' A ' does not meet t1.name=t2. Name to filter out the recordset, then this delete will not take effect. The record of the target table can be deleted under satisfied conditions. */merge into t2using t1on (t1.name=t2.name) when matched Thenupdateset T2. Money=t1. Money+t2. Moneydelete WHERE (t2.name = ' A ');

4. Insert in an unconditional manner

/* method is very easy, after writing a constant unequal condition (such as 1=2) in the syntax Onkeyword, the insert of the matched statement becomes unconditional insert, in details such as the following */merge into T2 USING T1 on (1=2) if 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 is only able to delete the target table. The source table cannot be deleted

/* This requires attention, regardless of whether the T2 of the delete where (T2.name = ' A ') is rewritten as T1. The effect is all the same, the target table is deleted. */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. Update the data in the same table, and worry about using the null value

SELECT * from T2;name------------------------------A 30C 20/* Requirements for self-updating of the T2 table. Assuming that a record of name=d is found in the T2 table, the Money field of the record is updated to 100, assuming that Name=d's record does not exist, it is added on its own initiative. Name=d and Money=100 's record. Based on the syntax, for example, the following code: */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. The T table should be added to the record because Name=d does not exist. But actually there is no change at all.                            Sql> SELECT * from T2;name money-------------------------------------------------------A   30C 20/* The original is because the select * from T2 where name= ' D ' is null, there is a case that cannot be inserted. We are able to use the value of the count (*) not to be an empty feature for equivalent modification. Details such as the following: */merge into t2using (select COUNT (*) CNT from T2 where name= ' D ') TON (t.cnt<>0) when matched Thenupdateset T2 . Money=100when not matched theninsertvalues (' D ', 100);              Sql> SELECT * from T2;name money-------------------------------A              30C 20D 100 

5. It is necessary to 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 run such as 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 at the time. The T1 table records two records corresponding to the T2 table, so there is an error.
The workaround is very easy. For example, we are able to Jianjian the main key of the T1 table and the T2 table, which is basically not possible, and in general, the associated fields of the merge statement have a primary key to each other. The efficiency of the merge will be higher! Or, make an aggregation of the ID column of the T1 table. Such errors can also be avoided by merging them into a single form.
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  SET T2. Money=t1. Money+t2. money;--under normal circumstances, the general appearance of repeated name need to arouse suspicion, not very should.

[Oracle] Merge statement