Oracle Merge into Usage

Oracle Merge into Usage

2011-08-24 10:52:54| Category: Oracle| Report | Font size Subscription

When updating data in peacetime, there is often an update that compares the data in the target table against the source table, and, if there is a record, updates the data in the target table against the values in the source table, and adds it to the target table if it does not exist. We can of course use two statements to handle this kind of data. However, there is a possibility that an exception will occur. As a result, Oracle added the merge statement in the 9i version to merge the update and INSERT statements. The statement can perform two steps in the same statement, reducing the number of INSERT and UPDATE statements that are executed. Merge is a deterministic statement that the same record will not be modified more than once in the same merge statement.
The specific syntax is:
MERGE [hint] into [schema.] table [T_alias] USING [schema.]
{Table | view | subquery} [T_alias] On (condition)
When matched then Merge_update_clause
When isn't matched then merge_insert_clause;

1.into clause
Specify the target table in the INTO clause that you want to modify or insert data into

2.using clause
Specify the data source to modify or insert in the Using clause. The data source can be a table, a view, or a subquery statement.

3.on clause
In the ON clause, specify the satisfying condition to perform the insertion or modification. In each row that meets the criteria in the target table, Oracle modifies the rows with the corresponding data from the data source. For those rows that do not meet the criteria, Oracle inserts the corresponding data from the data source.

4.when Matched | Not matched
Use this clause to inform Oracle how to make appropriate actions on the results that meet or do not meet the criteria. You can use the following two types of clauses.

5.merge_update clause
The MERGE_UPDATE clause performs a modification to the field values in the target table. Executes when an ON clause condition is met. If the modification clause is executed, the modification trigger on the target table is triggered.
Restriction: You cannot specify a default value when you modify a view

6.merge_insert clause
The Merge_insert clause executes when the ON clause condition is not met, inserting data into the target table.

The basic usage of the merge is listed below (the target table and the source table structure are the same in this case)
1) simultaneous use of matched and not matched
Merge into Target table A
Using source table B on (association condition A. Field 1 = B. Field 1)
When matched then
Update set a. field 2=b. Field 2,......
When isn't matched then
Insert (A. field 2,a. Field 3)
VALUES (b. field 2,b. field 3);
2) only not matched clause, that is, only insert not update
Merge into Target table A
Using source table B on (association condition A. Field 1 = B. Field 1)
When isn't matched then
Insert (A. field 2,a. Field 3)
VALUES (b. field 2,b. field 3);

3) only matched clause, i.e. only update not inserted
Merge into Target table A
Using source table B on (association condition A. Field 1 = B. Field 1)
When matched then
Update set a. field 2=b. Field 2,......

In addition, the update part of the merge command can contain a delete clause. The DELETE clause (with its own WHERE clause) can delete rows in the target table that were updated by merge. The Delete...where clause calculates the updated value instead of the initial value in the target table. If a row in the target table conforms to the delete...where condition but not within the rowset scope that the merge functions (as defined on the on condition), it is not deleted.
The specific syntax is:
Using source table B on (association condition A. Field 1 = B. Field 1)
When matched then
Update set a. field 2=b. Field 2,......
Delete where (A. field 5=b. field 5);

Note: The source table here can be more than just a table, or it can be a result set that is queried. This command is also applicable in DB2.

Oracle Merge into Usage