[Oracle] Merge statement

The syntax of Merge is as follows:

MERGE [hint] INTO [schema .] table [t_alias] USING [schema .] { table | view | subquery } [t_alias] ON ( condition ) WHEN MATCHED THEN merge_update_clause WHEN NOT MATCHED THEN merge_insert_clause;

What is MERGE and how to use it? Let's look at a simple requirement first:

The requirement is to update data from Table T1 to table T2. If the NAME of table T2 already exists in Table T1, the MONEY will be accumulated. If it does not exist, insert records from Table T1 to table T2.

As you know, in the case of equivalence, at least two statements, one for UPDATE and the other for INSERT, must be followed by the judgment logic or written in the process, for a single statement, you must write all the conditions,
Writing in the UPDATE and INSERT statements is troublesome and error-prone. If we understand MERGE, we can use a single SQL statement without using the stored procedure to implement the business logic. 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 major flexibility aspects of Merge show the syntax and basic usage of Merge. In fact, Merge can be very flexible. 1. Only one UPDATE and INSERT actions can appear (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; -- You 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. conditions can be added 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. Clear rows using the DELETE clause.

/* In this case, the T1.NAME = T2.NAME record must be satisfied first. If T2.NAME = 'A' does not meet the record set filtered by T1.NAME = T2.NAME, the DELETE operation does not take effect. You can DELETE the records of the target table if the deletion conditions are met. */Merge into T2USING T1ON (T1.NAME = T2.NAME) when matched thenupdateset T2.MONEY = T1.MONEY + T2.MONEYDELETE WHERE (T2.NAME = 'A ');

4. Use the unconditional Insert method.

/* The method is very simple. After the constant unequal conditions (such as 1 = 2) are written in the syntax ON keyword, the INSERT of the MATCHED statement becomes unconditional INSERT, the details are as follows */merge into T2 USING T1 ON (1 = 2) when not matched then insertvalues (T1.NAME, T1.MONEY );

Misunderstanding of Merge 1. Columns referenced by the ON clause cannot be updated.

Merge into T2USING T1ON (T1.NAME = T2.NAME) when matched thenupdateset T2.NAME = T1.NAME; ORA-38104: the column referenced in the ON clause cannot be updated: "T2". "NAME"

2. The WHERE order of the DELETE clause must be the 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 ended incorrectly

3. The DELETE clause can only DELETE the target table, but cannot DELETE the source table.

/* It should be noted that whether or not T2 in the delete where (T2.NAME = 'A') Statement is rewritten to T1, the results are the same, and the target table is deleted! */SELECT * FROM T1; name money ------------------ ---------- A 10B 20 SELECT * FROM T2; name money -------------------- ---------- A 30C 20 merge into 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 20 SELECT * FROM T2; name money -------------------- ---------- C 20

4. When updating data in the same table, you need to worry about the USING null value.

SELECT * FROM T2; name money ------------------ ---------- A 30C 20/* is required to perform self-Update on table T2. If A record with NAME = D is found in Table T2, update the MONEY field of the record to 100. If the record with NAME = D does not exist, the record with NAME = D and MONEY = 100 is automatically added. Run 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 = 100 when not matched theninsertvalues ('D', 200); -- but the query finds that, originally, the T table should add records because NAME = D does not exist, but it does not actually change. SQL> SELECT * FROM T2; NAME MONEY-------------------------------------------------------A 30C 20/* It is because the select * from t2 where NAME = 'D' is NULL, so there is a situation that cannot be inserted, we can use the feature that the COUNT (*) value is not null to perform equivalent transformation, as follows: */merge into T2USING (select COUNT (*) CNT from t2 where NAME = 'D') TON (T. CNT <> 0) when matched thenupdateset T2.MONEY = 100 when not matched theninsertvalues ('D', 100); SQL> SELECT * FROM T2; NAME MONEY-------------------------------A 30C 20D 100

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

--- Construct data, please note that insert a record here, it will produce the ORA-30926 error insert into T1 VALUES ('A', 30); COMMIT; --- continue executing the following merge into T2USING T1ON (T1.NAME = T2.NAME) when matched thenupdateset T2.MONEY = T1.MONEY + T2.MONEY; ORA-30926: you cannot obtain a stable set of rows in the source table/* The merge statement in oracle should ensure the uniqueness of the conditions in on. When T1.NAME = T2.NAME, table T1 records correspond to two records in Table T2, so an error occurs. The solution is simple. For example, you can create a primary and a secondary key for the associated fields of table T1 and table T2, which basically does not cause such a problem. In general, if the fields associated with the MERGE statement have primary keys, the efficiency of the MERGE statement will be relatively high! Or, you can combine the ID column of Table T1 into a single entry to avoid such errors. For example: */merge into T2 USING (select NAME, SUM (MONEY) as money from T1 group by name) T1 ON (T1.NAME = T2.NAME) when matched then update set T2.MONEY = T1.MONEY + T2.MONEY; -- under normal circumstances, duplicate names usually cause suspicion and should not be appropriate.