MERGE Statement

The MERGE statement inserts some rows and updates others in a single operation. The decision about whether to update or insert into the target table is based upon a join condition: rows already in the target table that match the join condition are updated; otherwise a row is inserted using values from a separate subquery.

For the syntax and full details of this statement, see the Oracle9i SQL Reference.

Usage Notes

This statement is primarily useful in data warehousing situations where large amounts of data are commonly inserted and updated.

Examples

The following example creates a bonuses table in the sample schema oe with a default bonus of 100. It then inserts into the bonuses table all employees who made sales (based on the sales_rep_id column of the oe.orders table). Finally, the Human Resources manager decides that all employees should receive a bonus:

• Those who have not made sales get a bonus of 1% of their salary.
• Those who already made sales get an increase in their bonus equal to 1% of their salary.

The MERGE statement implements these changes in one step:

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);
INSERT INTO bonuses(employee_id)
(SELECT e.employee_id FROM employees e, orders o
WHERE e.employee_id = o.sales_rep_id
GROUP BY e.employee_id);
SELECT * FROM bonuses;
EMPLOYEE_ID BONUS
----------- ----------
153 100
154 100
155 100
156 100
158 100
159 100
160 100
161 100
163 100
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.1);

EMPLOYEE_ID BONUS
----------- ----------
153         180
154         175
155         170
156         200
158         190
159         180
160         175
161         170
163         195
157         950
145         1400
170         960
179         620
152         900
169         1000
...