Combining INSERT and UPDATE with MERGE

The MERGE statement (also known as "upsert") lets you insert new rows and update existing ones in a single, atomic operation. It's much cleaner and faster than writing separate INSERT and UPDATE statements with existence checks.

Recipe #1 - Basic MERGE statement

Suppose you have a staging table with new data that needs to be merged into a target table:

MERGE INTO employees tgt
USING employees_staging src
ON (tgt.employee_id = src.employee_id)
WHEN MATCHED THEN
  UPDATE SET
    tgt.salary     = src.salary,
    tgt.department = src.department,
    tgt.updated_at = SYSDATE
WHEN NOT MATCHED THEN
  INSERT (employee_id, name, salary, department, created_at)
  VALUES (src.employee_id, src.name, src.salary, src.department, SYSDATE);

Recipe #2 - MERGE with DELETE clause

Since Oracle 10g, you can also delete rows as part of a MERGE. The DELETE only affects rows that were updated by the MATCHED clause:

MERGE INTO products tgt
USING product_updates src
ON (tgt.product_id = src.product_id)
WHEN MATCHED THEN
  UPDATE SET
    tgt.price  = src.price,
    tgt.status = src.status
  DELETE WHERE tgt.status = 'DISCONTINUED'
WHEN NOT MATCHED THEN
  INSERT (product_id, name, price, status)
  VALUES (src.product_id, src.name, src.price, src.status);

Recipe #3 - MERGE with a subquery

You don't need a staging table — you can use a subquery or even a VALUES clause as the source:

MERGE INTO config_settings tgt
USING (
  SELECT 'MAX_RETRIES' AS key, '5' AS value FROM DUAL
) src
ON (tgt.key = src.key)
WHEN MATCHED THEN
  UPDATE SET tgt.value = src.value
WHEN NOT MATCHED THEN
  INSERT (key, value) VALUES (src.key, src.value);

MERGE is one of the most powerful SQL statements in Oracle and should be your go-to whenever you need conditional insert/update logic.