Oracle SQL Merge Statement

The Oracle SQL Merge Statement is a powerful utility that allows the combination of inserts, updates and deletes all in one statement. It is particularly useful in data warehousing operations.

Let's review an example. Suppose under a hypothetical scenario, there is an employee and bonus table that contains a flat bonus for all employees in a sales position(commission pct. is not null).

Suppose HR decided that all sales employees should receive an additional bonus of 1% of their salary, if their salary was less than $50000. However, all other employees not in sales receive a bonus of 10% of their salary if it is equal to or less than $50000. The appropriate SQL Merge statement is depicted below:

MERGE INTO Bonus B
USING (SELECT employee_id,salary,department_id
FROM employees) S
ON(S.employee_id=B.employee_id)
WHEN MATCHED THEN
UPDATE SET B.Bonus=round(B.Bonus+0.01*S.salary,2)
DELETE WHERE (S.salary >50000)
WHEN NOT MATCHED THEN
INSERT (B.employee_id,B.Bonus) VALUES (S.employee_id,round(S.salary*0.1,2))
WHERE(S.salary<=50000); SELECT B.Bonus,B.employee_id,S.salary,S.commission_pct FROM employees S LEFT OUTER JOIN Bonus B on B.employee_id = S.employee_id ORDER BY B.employee_id;

Now for an explanation:
MERGE INTO:
List the table you want to either insert, update or delete data.
USING:
List the source data; can be either a table, view or subquery.
ON:
Represents the condition you want to match the source and target table on.
WHEN MATCHED:
If there is a match, perform and update and possible delete based on the WHERE condition.
WHEN NOT MATCHED:
If there is no match, perform an insert and possible delete based on the WHERE condition.

And there you have it, a complex, but useful data warehousing solution.