MERGE Concept

Definition

MERGE (also called "upsert" in dialects that spell it differently — see 11.7) combines INSERT, UPDATE, and sometimes DELETE into a single statement: given a source of incoming rows, it inserts rows that don't yet exist and updates rows that already do, based on a matching condition — all in one atomic operation.

How It Works

Standard SQL MERGE syntax (supported by SQL Server, Oracle, PostgreSQL 15+):

sql
MERGE INTO employees AS target
USING new_employee_data AS source
ON target.id = source.id
WHEN MATCHED THEN
  UPDATE SET target.salary = source.salary
WHEN NOT MATCHED THEN
  INSERT (id, name, department, salary) VALUES (source.id, source.name, source.department, source.salary);

For each row in source, this checks whether a matching id already exists in target: if it does (WHEN MATCHED), update it; if not (WHEN NOT MATCHED), insert it. This solves a genuinely common real problem — syncing a target table with a batch of "here's the current state" data, without knowing in advance which rows are new and which already exist.

Edge Cases and Pitfalls

• Not every dialect supports the standard MERGE syntax — MySQL notably does not have MERGE, relying instead on INSERT ... ON DUPLICATE KEY UPDATE (11.7) to achieve the same upsert effect with different syntax.
• MERGE can optionally include a WHEN NOT MATCHED BY SOURCE clause (in some dialects) to DELETE target rows that no longer appear in the source at all — turning MERGE into a full three-way sync (insert new, update changed, delete removed), not just insert-or-update.
• Doing the same logic manually as two separate statements (an UPDATE followed by an INSERT ... WHERE NOT EXISTS) works but isn't atomic in the same way — a MERGE statement is one indivisible operation, reducing the risk of a race condition between the two steps under concurrent access.

Key Takeaways

• MERGE combines insert-if-absent and update-if-present logic into one atomic statement, matching source rows against target rows on a condition.
• Not every dialect has MERGE — MySQL uses a different syntax (ON DUPLICATE KEY UPDATE, 11.7) for the same concept.
• MERGE can optionally also delete target rows no longer present in the source, for a full sync operation.