Using Merge Statment in Oracle PL/SQL

In Oracle database, the MERGE statement is designed to combine multiple operations to reduce the complexity of mixed insert and update operations. MERGE allows you to avoid multiple INSERT, UPDATE, and DELETE DML statements by combining the operations into a single statement.

Merge Syntax

The syntax of the MERGE statement is:

MERGE [hint] INTO [schema .]table [t_alias]
USING [[schema .]table | view | subquery] t_alias
ON ( condition ) [merge_update_clause | merge_insert_clause]

merge_update_clause:

WHEN MATCHED THEN UPDATE SET [<column> = [<expr>|DEFAULT][,]]<where_clause>
DELETE <where_clause>

merge_insert_clause:

WHEN NOT MATCHED THEN INSERT ( <column> [,])
VALUES (<expr>|DEFAULT[,])
<where_clause>

where_clause:

The clauses in the MERGE statement have the following definitions.

INTO Clause - The INTO clause is used to specify the target table into which you are inserting or updating.

USING Clause - The USING clause specifies the source of the data to be updated or inserted. The source for a MERGE statement can be a table, view, or the result of a subquery.

ON Clause - The ON clause specifies the condition that the MERGE operation uses to determine whether it updates or inserts. When the search condition evaluates to true, Oracle updates the row in the target table with corresponding data from the MERGE source. If no rows satisfy the condition, then Oracle inserts the row into the target table based on the corresponding MERGE source row.

merge_update_clause - The merge_update_clause is used to specify the update column values of the target table. Oracle performs the specified update if the condition of the ON clause is true. As with any normal update, when the update clause is executed, all update triggers defined on the target table are fired.

where_clause - You must specify the where_clause if you want Oracle to execute the update operation only if the specified condition is true. The WHERE condition can apply to either the data source or the target table. If the condition is false, the update operation is skipped when merging the row into the target table.

You can specify the DELETE where_clause to clean up data in a table while the MERGE statement is populating or updating it. The only rows affected by the delete clause of the MERGE statement are those rows in the target table that are updated by the merge operation.

This means the DELETE WHERE condition evaluates the updated value, not the original value of the row. Even if a row of the target table satisfies the DELETE condition but is not included in the data set from the join defined by the MERGE's ON clause, then it is not deleted. If the MERGE statement deletes a row, any delete triggers defined on the target table will be activated for each row deletion.

Merge Statement Example

CREATE TABLE employee (
employee_id NUMBER(5),
first_name  VARCHAR2(20),
last_name   VARCHAR2(20),
dept_no     NUMBER(2),
salary      NUMBER(10));

INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000);
INSERT INTO employee VALUES (2, 'Helen', 'Lofstrom', 20, 100000);
INSERT INTO employee VALUES (3, 'Akiko', 'Toyota', 20, 50000);
INSERT INTO employee VALUES (4, 'Jackie', 'Stough', 20, 40000);
INSERT INTO employee VALUES (5, 'Richard', 'Foote', 20, 70000);
INSERT INTO employee VALUES (6, 'Joe', 'Johnson', 20, 30000);
INSERT INTO employee VALUES (7, 'Clark', 'Urling', 20, 90000);

CREATE TABLE bonuses (
employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses (employee_id) VALUES (1);
INSERT INTO bonuses (employee_id) VALUES (2);
INSERT INTO bonuses (employee_id) VALUES (4);
INSERT INTO bonuses (employee_id) VALUES (6);
INSERT INTO bonuses (employee_id) VALUES (7);
COMMIT;


MERGE INTO bonuses b
USING (
  SELECT employee_id, salary, dept_no
  FROM employee
  WHERE dept_no =20) e
ON (b.employee_id = e.employee_id)
WHEN MATCHED THEN
  UPDATE SET b.bonus = e.salary * 0.1
  DELETE WHERE (e.salary < 40000)
WHEN NOT MATCHED THEN
  INSERT (b.employee_id, b.bonus)
  VALUES (e.employee_id, e.salary * 0.05)
  WHERE (e.salary > 40000);