Explain the MERGE statement in Oracle SQL

How it works

MERGE does an 'upsert' in one statement — it joins a source to a target on a matching condition, then runs a WHEN MATCHED THEN UPDATE and/or a WHEN NOT MATCHED THEN INSERT depending on whether that join found a row. This avoids a separate UPDATE-then-INSERT-the-rest pattern (and the extra round trips and race conditions that come with it) for common synchronization tasks like loading a staging table into a target.

MERGE INTO employees_target t
USING employees_staging s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
  UPDATE SET t.salary = s.salary, t.last_name = s.last_name
WHEN NOT MATCHED THEN
  INSERT (employee_id, last_name, salary)
  VALUES (s.employee_id, s.last_name, s.salary);

Related Questions