Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Statements and Clauses

SQL UPSERT (INSERT ... ON CONFLICT)

Reference for the SQL upsert: insert a row or update it when a unique key already exists, with ON CONFLICT DO UPDATE and DO NOTHING plus MySQL and MERGE forms.

An upsert inserts a row, or changes the existing row when the insert would collide with a primary key or unique constraint. SQLite and PostgreSQL spell it INSERT ... ON CONFLICT. Doing the decision inside one statement is shorter than "check, then insert or update", and it is safe when several clients write at the same time.

The ON CONFLICT clause names the unique column (the conflict target) and an action: DO NOTHING to skip the new row, or DO UPDATE SET ... to change the existing one. Inside DO UPDATE, the pseudo-table excluded holds the values you tried to insert.

SQL
INSERT INTO inventory (sku, quantity)
VALUES ('MG-200', 35)
ON CONFLICT (sku) DO UPDATE
SET quantity = excluded.quantity;

Try it

SQL Update on conflict, insert otherwise

Press Run (or Ctrl + Enter) to see the result here.

SQL DO NOTHING keeps the original row ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
INSERT INTO table_name (columns)
VALUES (values)
ON CONFLICT (conflict_columns) DO NOTHING;

INSERT INTO table_name (columns)
VALUES (values)
ON CONFLICT (conflict_columns) DO UPDATE
SET column = expression [, ...]
[WHERE condition];
Part Description
conflict_columns The column or columns of a PRIMARY KEY, UNIQUE constraint or unique index that defines "the same row".
DO NOTHING Skip the row that conflicted; other rows in the statement are still inserted.
DO UPDATE SET Update the existing row. Plain column names (or the table name) mean the old row; excluded.column means the proposed new row.
WHERE Optional filter: update only when the condition is true, otherwise leave the row alone.

Dialect differences

Dialect Spelling
SQLite ON CONFLICT (cols) DO UPDATE / DO NOTHING (3.24+). Also INSERT OR IGNORE and INSERT OR REPLACE.
PostgreSQL The same ON CONFLICT syntax (9.5+).
MySQL INSERT ... ON DUPLICATE KEY UPDATE col = VALUES(col), and INSERT IGNORE. No conflict target: any unique key can trigger it.
SQL Server No ON CONFLICT; use MERGE, or UPDATE followed by INSERT ... WHERE NOT EXISTS in a transaction.
Oracle MERGE INTO ... USING ... WHEN MATCHED / NOT MATCHED.
SQL
-- MySQL
INSERT INTO inventory (sku, quantity)
VALUES ('MG-200', 35)
ON DUPLICATE KEY UPDATE quantity = VALUES(quantity);

-- Oracle and SQL Server (MERGE; add a semicolon in SQL Server)
MERGE INTO inventory AS t
USING (SELECT 'MG-200' AS sku, 35 AS quantity) AS s
  ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET quantity = s.quantity
WHEN NOT MATCHED THEN INSERT (sku, quantity) VALUES (s.sku, s.quantity);

Usage notes

  • A unique constraint is required. Naming columns that are not unique in ON CONFLICT is an error.
  • INSERT OR REPLACE is not an update. It deletes the conflicting row and inserts a new one, so columns you did not supply are reset to defaults, the rowid can change, and delete triggers and cascades may fire. Prefer DO UPDATE.
  • Update relative to the old value with inventory.quantity + excluded.quantity; use excluded.quantity alone to overwrite.
  • Row-by-row feeds work: every row in a multi-row VALUES list is checked separately.
  • One target per clause: each ON CONFLICT clause names one unique key. SQLite 3.35+ allows several clauses, so different unique keys can be handled differently.
  • Combine with RETURNING to read the final row, whether inserted or updated.
  • Concurrent safety: the upsert is atomic with respect to the unique key, unlike a separate SELECT followed by INSERT.