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.
INSERT INTO inventory (sku, quantity) VALUES ('MG-200', 35) ON CONFLICT (sku) DO UPDATE SET quantity = excluded.quantity;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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. |
-- 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 CONFLICTis an error. INSERT OR REPLACEis 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. PreferDO UPDATE.- Update relative to the old value with
inventory.quantity + excluded.quantity; useexcluded.quantityalone to overwrite. - Row-by-row feeds work: every row in a multi-row
VALUESlist is checked separately. - One target per clause: each
ON CONFLICTclause names one unique key. SQLite 3.35+ allows several clauses, so different unique keys can be handled differently. - Combine with
RETURNINGto read the final row, whether inserted or updated. - Concurrent safety: the upsert is atomic with respect to the unique key, unlike a separate
SELECTfollowed byINSERT.