Oracle MERGE Statement: Upsert Patterns and Best Practices

What MERGE does

MERGE synchronises a target table with a source dataset in one pass:

• If a matching row exists in target: UPDATE it (or DELETE it)
• If no matching row exists in target: INSERT it

One MERGE statement replaces the common “check if exists, then insert or update” pattern that requires multiple round trips.

Basic syntax

MERGE INTO orders tgt
USING staging_orders src
ON (tgt.order_ref = src.order_ref)
WHEN MATCHED THEN
  UPDATE SET
    tgt.status     = src.status,
    tgt.updated_dt = SYSDATE
  WHERE tgt.status != src.status  -- update only when changed
WHEN NOT MATCHED THEN
  INSERT (order_id, order_ref, customer_id, amount, status, created_dt)
  VALUES (seq_order.NEXTVAL, src.order_ref, src.customer_id, src.amount, src.status, SYSDATE);

Adding DELETE to MERGE

Delete records in the target that should no longer exist:

WHEN MATCHED THEN
  UPDATE SET tgt.status = src.status
  DELETE WHERE src.active_flag = 'N';

The DELETE clause runs after the UPDATE — it deletes the updated row if the WHERE condition is true.

MERGE with subquery source

The source can be any query, not just a table:

MERGE INTO employee_summary tgt
USING (
  SELECT department_id, COUNT(*) AS headcount, AVG(salary) AS avg_sal
  FROM employees GROUP BY department_id
) src
ON (tgt.department_id = src.department_id)
WHEN MATCHED THEN
  UPDATE SET tgt.headcount = src.headcount, tgt.avg_salary = src.avg_sal
WHEN NOT MATCHED THEN
  INSERT (department_id, headcount, avg_salary)
  VALUES (src.department_id, src.headcount, src.avg_sal);

Tracking MERGE results

MERGE INTO orders tgt USING staging src ON ...
...;

DBMS_OUTPUT.PUT_LINE('Rows merged: ' || SQL%ROWCOUNT);

SQL%ROWCOUNT after MERGE returns the total number of rows processed (inserts + updates + deletes).

MERGE in FBDI and OIC integrations

MERGE is ideal for staging table patterns in FBDI automation:

1. Load raw data into a staging table
2. MERGE staging into the clean target table (handle duplicates, updates, new records in one step)
3. Clear staging after successful MERGE

This avoids separate DELETE/INSERT cycles that create unnecessary undo/redo.

Performance: avoid MERGE on indexed columns in WHERE

If the MATCHED UPDATE has a WHERE clause that references indexed columns, Oracle may not use those indexes efficiently. Test execution plans on large datasets before deploying MERGE statements in batch jobs.