Oracle merge into usage

Purpose:
The merge command can be used to modify or insert data from one table to another. The insert or modify operation depends on the conditions of the on clause.

This statement can be executed in two steps in the same statement, which can reduce the number of insert and update statements.

Merge is a deterministic statement that does not modify the same record multiple times in the same merge statement.

Syntax:
1. into clause
Specify the target table to be modified or inserted in the into clause.

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

3. on Clause
In the on clause, specify the conditions to be met for executing the insert or modify operation. In each row that meets the conditions in the target table, oracle uses the corresponding data in the data source to modify these rows. For rows that do not meet the conditions, oracle inserts the corresponding data in the data source.

4. when matched | not matched
Use this clause to notify oracle how to perform operations on results that meet or do not meet the conditions. You can use the following two types of clauses.

5. merge_update clause
The merge_update clause modifies the Field Values in the target table. Executed when the on Clause condition is met. If the modification clause is executed, the modification trigger on the target table is triggered.
Restriction: a default value cannot be specified when a view is modified.

6. merge_insert clause
If the execution of the merge_insert clause does not meet the conditions of the on clause, insert data to the target table. If the insert clause is executed, the insert trigger on the target table is triggered.
. Restriction: a default value cannot be specified when a view is modified.

Merge into bonuses d

Using (select employee_id, salary, department_id from employees

Where department_id = 80) s

On (d. employee_id = s. employee_id)

When matched then update set d. bonus = d. bonus + s. salary *. 01

When not matched then insert (d. employee_id, d. bonus)

Values (s. employee_id, s. salary * 0.01 );