Upsert Syntax Compared: ON DUPLICATE KEY, ON CONFLICT, MERGE

1. What upsert means and why every database solves it differently

The term upsert combines update and insert and describes a single statement that updates a row if it already exists and inserts it otherwise. This pattern shows up constantly in application development: synchronizing external data, caching tables, configuration values, counter values, and import routines almost always need exactly this semantic. Without native support you would first have to check whether a row exists, then conditionally insert or update, which means two round trips to the database and a race condition between the two steps.

The ANSI SQL standard has offered the MERGE statement as a generic mechanism for this task since SQL:2003, yet the major databases invented their own, partly incompatible approaches long before standardization. MySQL introduced its own extension with ON DUPLICATE KEY UPDATE, PostgreSQL followed much later with ON CONFLICT, and SQLite offers both its own INSERT OR REPLACE variant and, more recently, a PostgreSQL-like ON CONFLICT. Anyone migrating code between these systems or writing an application that supports multiple databases needs to know the differences in syntax, semantics, and atomicity precisely to avoid producing silent bugs.

2. MySQL: INSERT ... ON DUPLICATE KEY UPDATE in detail

MySQL solves upsert through the ON DUPLICATE KEY UPDATE clause, which is appended directly to a normal INSERT. A conflict occurs when the row being inserted violates a PRIMARY KEY or a UNIQUE index. In that case the specified update clause runs instead of the insert, and VALUES(column), or since MySQL 8.0.19 an explicit alias, can be used to access the values that were originally meant to be inserted. This syntax is proprietary and has no equivalent in any other major database in this exact form.

One MySQL quirk: when several unique constraints are violated simultaneously, it is not always predictable which index triggers the conflict first, which can lead to surprising behavior in tables with multiple unique keys. In addition, MySQL counts affected_rows for an actual update as two instead of one whenever at least one value changes, a detail that many ORM integrations misinterpret when they use the return value for success checks.

-- MySQL: INSERT ... ON DUPLICATE KEY UPDATE
CREATE TABLE product_stock (
  sku VARCHAR(64) PRIMARY KEY,
  quantity INT NOT NULL,
  updated_at DATETIME NOT NULL
);

-- Upsert: insert new SKU or update quantity if it already exists
INSERT INTO product_stock (sku, quantity, updated_at)
VALUES ('SKU-1001', 42, NOW())
AS new_values
ON DUPLICATE KEY UPDATE
  quantity = new_values.quantity,
  updated_at = new_values.updated_at;

-- Older syntax without the row alias (still widely used, MySQL < 8.0.19)
INSERT INTO product_stock (sku, quantity, updated_at)
VALUES ('SKU-1002', 17, NOW())
ON DUPLICATE KEY UPDATE
  quantity = VALUES(quantity),
  updated_at = VALUES(updated_at);

3. PostgreSQL: INSERT ... ON CONFLICT in detail

PostgreSQL has taken a noticeably more explicit approach with ON CONFLICT since version 9.5. Unlike MySQL, PostgreSQL allows the triggering constraint to be specified by name or by a column list, which guarantees unambiguous behavior when multiple unique constraints exist. Two variants are available: DO NOTHING ignores the conflict entirely and leaves the existing row unchanged, while DO UPDATE SET performs a targeted update, using EXCLUDED.column to access the values that were actually meant to be inserted, analogous to VALUES() in MySQL.

A strong PostgreSQL feature is the ability to add an extra WHERE condition to the DO UPDATE clause, so an update only applies when a specific condition is met, for example when the incoming timestamp is newer than the stored one. This conditional update replaces the need for separate optimistic locking in many cases and cannot be replicated directly in MySQL without additional application logic.

-- PostgreSQL: INSERT ... ON CONFLICT
CREATE TABLE product_stock (
  sku VARCHAR(64) PRIMARY KEY,
  quantity INTEGER NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL
);

-- Upsert with explicit conflict target and EXCLUDED reference
INSERT INTO product_stock (sku, quantity, updated_at)
VALUES ('SKU-1001', 42, now())
ON CONFLICT (sku) DO UPDATE SET
  quantity = EXCLUDED.quantity,
  updated_at = EXCLUDED.updated_at
-- Conditional update: only apply if the incoming row is newer
WHERE product_stock.updated_at < EXCLUDED.updated_at;

-- Ignore duplicates entirely instead of updating
INSERT INTO product_stock (sku, quantity, updated_at)
VALUES ('SKU-1002', 17, now())
ON CONFLICT (sku) DO NOTHING;

4. SQL Server and Oracle: MERGE as the standard-closer approach

Both SQL Server and Oracle rely on the significantly more powerful, but also significantly more verbose, MERGE statement for upsert tasks, which comes closest to the ANSI standard. MERGE compares a target table with a source using an ON condition and allows separate branches for WHEN MATCHED, WHEN NOT MATCHED, and in Oracle additionally WHEN NOT MATCHED BY SOURCE, which even lets you integrate deletion of rows that no longer appear in the source. This flexibility makes MERGE the tool of choice for complex synchronization tasks between two tables, not just for single rows.

The price of this flexibility is noticeably more boilerplate code for the simple case of a single row, and SQL Server had documented race-condition issues at high concurrency in older versions when MERGE was used without additional transaction isolation. Microsoft has explicitly recommended for several versions that, for pure upsert needs, simpler patterns using UPDATE followed by a conditional INSERT within the same transaction be used instead, unless complex multi-row synchronization is actually required.

-- SQL Server / Oracle: MERGE for upsert semantics
MERGE INTO product_stock AS target
USING (VALUES ('SKU-1001', 42)) AS source (sku, quantity)
ON target.sku = source.sku
WHEN MATCHED THEN
  UPDATE SET
    target.quantity = source.quantity,
    target.updated_at = SYSDATETIME()
WHEN NOT MATCHED THEN
  INSERT (sku, quantity, updated_at)
  VALUES (source.sku, source.quantity, SYSDATETIME());

-- Oracle variant uses SYSTIMESTAMP instead of SYSDATETIME
-- and requires a terminating semicolon on the MERGE statement itself

5. SQLite: INSERT OR REPLACE and its ON CONFLICT counterpart

SQLite ships with its own, older solution called INSERT OR REPLACE, which fully deletes the existing row on a constraint violation and replaces it with the new one, instead of updating individual columns. That sounds like the same result as an update, but there is a crucial difference: delete and re-insert trigger related triggers and foreign key cascades that would never fire on a real update, and an AUTOINCREMENT value of the deleted row is lost if no explicit primary key value is provided.

Since SQLite 3.24 the database additionally supports a PostgreSQL-compatible ON CONFLICT syntax with DO UPDATE SET and excluded.column, which avoids exactly the problems of INSERT OR REPLACE because it performs a genuine targeted update instead of delete-then-insert. For new SQLite projects, the ON CONFLICT variant is almost always preferable, especially when triggers or foreign key relationships are involved.

-- SQLite: legacy INSERT OR REPLACE (delete-then-insert semantics)
INSERT OR REPLACE INTO product_stock (sku, quantity, updated_at)
VALUES ('SKU-1001', 42, datetime('now'));
-- Warning: triggers a DELETE and INSERT internally, fires related triggers twice

-- SQLite 3.24+: PostgreSQL-compatible ON CONFLICT, true update semantics
INSERT INTO product_stock (sku, quantity, updated_at)
VALUES ('SKU-1001', 42, datetime('now'))
ON CONFLICT(sku) DO UPDATE SET
  quantity = excluded.quantity,
  updated_at = excluded.updated_at;

6. Race conditions and why upsert is not just upsert

The actual reason to implement upsert syntax in the database rather than in the application layer is atomicity. A manual pattern of SELECT followed by a conditional UPDATE or INSERT has a time window between the two statements during which a concurrent transaction can insert the same row. Without additional locking this leads either to a constraint error or a duplicate row, depending on whether a unique constraint exists. Native upsert statements execute the entire check and update within a single atomic server-side operation and close this time window completely.

It is still important to keep the transaction isolation level in mind. PostgreSQL guarantees atomicity for ON CONFLICT even under REPEATABLE READ, while MySQL with InnoDB sometimes needs additional gap locks for ON DUPLICATE KEY UPDATE, which can lead to more deadlocks than expected under high concurrency. Anyone running upsert operations in batches with high parallelism should check the specific locking documentation of the database in use rather than blindly assuming atomicity.

7. Portable application-layer strategies

Anyone writing an application that must support multiple databases should hide upsert logic behind its own abstraction layer instead of embedding native syntax directly into business logic. A common pattern is a repository method upsert(table, key, values) that generates the appropriate syntax internally depending on the database driver. Most modern ORMs like Doctrine, Eloquent, or SQLAlchemy already offer such an abstraction, though the statements they generate can differ in detail, which is why a look at the generated SQL before going to production is worthwhile.

If an ORM abstraction is not available or too imprecise, runtime feature detection based on driver metadata, combined with database-specific SQL templates per supported system, helps. It is important to explicitly test the conflict case in tests, not just the fresh-insert case, since this exact branch varies the most between databases and is, in practice, the least commonly tested automatically.

-- Portable pattern: explicit two-step upsert as a database-agnostic fallback
-- Works identically on any SQL database that supports transactions
BEGIN;

UPDATE product_stock
SET quantity = 42, updated_at = CURRENT_TIMESTAMP
WHERE sku = 'SKU-1001';

-- If no row was updated, the row does not exist yet, insert it
INSERT INTO product_stock (sku, quantity, updated_at)
SELECT 'SKU-1001', 42, CURRENT_TIMESTAMP
WHERE NOT EXISTS (
  SELECT 1 FROM product_stock WHERE sku = 'SKU-1001'
);

COMMIT;
-- Note: still needs SERIALIZABLE isolation or a unique constraint
-- plus retry-on-conflict logic to be fully race-condition-safe

8. Performance differences between the upsert variants

With large data volumes, clear performance differences appear between the approaches. MySQL's ON DUPLICATE KEY UPDATE is well optimized for batch inserts with many values in a single statement and scales linearly with the number of rows. PostgreSQL's ON CONFLICT behaves similarly efficiently, but on very wide tables with many columns to update it has a measurable overhead from the internal conflict check performed before the actual write.

MERGE in SQL Server and Oracle is optimized for multi-row synchronization, but for single rows it carries a higher parsing and planning overhead since the optimizer must always evaluate the full join semantics between target and source. INSERT OR REPLACE in SQLite tends to be slower than the newer ON CONFLICT variant on tables with many foreign key relationships, due to the internal delete and re-insert, since it triggers additional cascade checks that a real update would not need.

9. Upsert syntax compared directly

The following table summarizes the key differences between the upsert mechanisms discussed and highlights what deserves particular attention when migrating between the systems.

Anyone migrating between these systems should pay particular attention to the delete-plus-insert semantics of INSERT OR REPLACE, since foreign key relationships and triggers behave fundamentally differently from a real update. For SQL Server and Oracle it is also worth checking the reported MERGE behavior under high concurrency before using the statement for critical counter values.

10. Summary

Upsert syntax differs so much between MySQL, PostgreSQL, SQL Server, Oracle, and SQLite that a direct copy-paste approach during migrations almost always fails. MySQL uses the proprietary ON DUPLICATE KEY UPDATE clause, PostgreSQL relies on the more flexible ON CONFLICT with explicit conflict targets and conditional updates, SQL Server and Oracle follow the more ANSI-aligned but noticeably more verbose MERGE, and SQLite offers both an older delete-plus-insert variant and a modern, PostgreSQL-compatible ON CONFLICT.

For production code, an abstraction layer that hides the respective upsert syntax behind a unified interface pays off, combined with explicit tests for the conflict case and a deliberate choice of the appropriate isolation level. Knowing these upsert syntax differences prevents silent bugs during database switches and produces more portable SQL code from the start.

11. FAQ: Upsert Syntax Compared Across Databases