SQLite UPSERT: Insert or Update on Conflict

SQLite UPSERT lets an INSERT statement take another action when a row conflicts with a PRIMARY KEY, UNIQUE constraint, or unique index. Use DO NOTHING to skip the conflicting row, or DO UPDATE to change the existing row. SQLite added UPSERT in version 3.24.0; check your library with SELECT sqlite_version(); if the syntax is not recognized.

The phrase ON CONFLICT is also used for SQLite’s older constraint conflict-resolution clauses, such as INSERT OR IGNORE. The UPSERT syntax covered here is the INSERT ... ON CONFLICT ... DO ... clause. See the official SQLite UPSERT documentation for the full grammar.

Create a table with a unique key

The conflict target must refer to a uniqueness constraint. This table uses sku as its primary key:

CREATE TABLE inventory (
  sku TEXT PRIMARY KEY,
  product_name TEXT NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 0
);

Insert a row or update it on conflict

Insert a new product if its SKU is not present. If the SKU already exists, update its name and add the incoming quantity to the stored quantity:

INSERT INTO inventory (sku, product_name, quantity)
VALUES ('BK-15', 'Book', 5)
ON CONFLICT(sku) DO UPDATE SET
  product_name = excluded.product_name,
  quantity = inventory.quantity + excluded.quantity;

The excluded qualifier refers to the values that the INSERT attempted to add. In this example, the first execution creates a row with quantity 5. If you run the statement again for the same SKU with quantity 3, SQLite updates the existing row to quantity 8.

To replace the stored quantity instead of adding to it, write quantity = excluded.quantity. To skip a duplicate without updating the existing row, use:

INSERT INTO inventory (sku, product_name, quantity)
VALUES ('BK-15', 'Book', 5)
ON CONFLICT(sku) DO NOTHING;

Update only when the incoming value is newer

You can add a WHERE condition to the DO UPDATE action. The conflicting row is updated only when the condition is true:

CREATE TABLE contacts (
  email TEXT PRIMARY KEY,
  display_name TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

INSERT INTO contacts (email, display_name, updated_at)
VALUES ('[email protected]', 'Sam', '2026-09-25 12:00:00')
ON CONFLICT(email) DO UPDATE SET
  display_name = excluded.display_name,
  updated_at = excluded.updated_at
WHERE excluded.updated_at > contacts.updated_at;

If the stored contact is newer than or the same age as the incoming row, the statement leaves it unchanged.

Handle conflicts in a multi-row insert

SQLite makes the UPSERT decision separately for each row in a multi-row insert:

INSERT INTO inventory (sku, product_name, quantity)
VALUES
  ('BK-15', 'Book', 5),
  ('PN-02', 'Pen', 4),
  ('BK-16', 'Notebook', 2)
ON CONFLICT(sku) DO UPDATE SET
  product_name = excluded.product_name,
  quantity = inventory.quantity + excluded.quantity;

New SKUs are inserted and an existing SKU follows the update expressions. The official UPSERT reference describes how each row is processed.

Common mistakes

• No matching unique constraint: ON CONFLICT(sku) needs a PRIMARY KEY, UNIQUE constraint, or unique index on sku. A conflict on a non-unique column does not activate UPSERT.
• Expecting it to handle every constraint error: UPSERT only handles uniqueness conflicts. It does not intercept NOT NULL, CHECK, foreign key, or trigger-generated constraint failures.
• Confusing UPSERT with INSERT OR REPLACE: REPLACE deletes the conflicting row before inserting a new one; it is not an in-place update. See SQLite’s constraint conflict-resolution rules.
• A second constraint fails during DO UPDATE: SQLite’s DO UPDATE action uses the ABORT behavior. If its update causes another constraint violation, the statement aborts.
• Using UPSERT on an old SQLite library: the feature requires SQLite 3.24.0 or later. Check the runtime SQLite version, which may differ from the command-line version bundled with your operating system or application.

For dialect-specific alternatives, see MySQL UPSERT, PostgreSQL UPSERT, and SQL Server UPSERT.