SQLite UPSERT: Update the Same Record Without Replacing Its Identity

Reviewed October 9, 2026. Examples use Python's sqlite3 interface and synthetic inventory records.

An import runs twice because a network acknowledgment was lost. Should the second run create another product, overwrite a newer update, or do nothing? The answer depends on record identity and revision policy. An UPSERT can implement that policy, but it cannot discover it from a CSV file or a convenient column name.

SQLite UPSERT adds conflict handling to an insertion when a uniqueness constraint would be violated. It can update the existing row or skip the proposed insertion. This tutorial connects the SQL syntax to the practical questions that matter: what identifies the same entity, when an incoming value wins, and which other constraint failures should remain visible.

1. Define identity with a real uniqueness constraint

Choose a key that represents the entity consistently across imports. A product code might work if the source guarantees that codes are stable and never reused. A human-readable name usually does not. The database must encode the chosen identity with a primary key, unique constraint, or suitable unique index; application-side assumptions alone do not provide a conflict target.

Composite identity is common. A source system identifier and external product identifier together can distinguish records whose external IDs collide across systems. Include both in the uniqueness rule and in the conflict target. A synthetic integer primary key can coexist with this external identity and remain stable while incoming attributes change.

Nullable identity fields require special care. SQLite uniqueness permits multiple NULL values in ordinary unique constraints, so a nullable external identifier may fail to identify duplicates as intended. Use NOT NULL where the contract requires an identity, and reject incomplete keys before an import. A uniqueness rule is meaningful only when the stored key represents the intended entity.

2. Read excluded as the proposed incoming row

Inside DO UPDATE, references to existing columns describe the current stored row, while excluded.column refers to the value proposed by the attempted insertion. This distinction allows replacement, accumulation, and conditional revision policies. Each policy has different replay behavior, even when the SQL statements look similar.

import sqlite3

with sqlite3.connect(":memory:") as db:
    db.execute("CREATE TABLE product (id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE, stock INTEGER NOT NULL CHECK(stock >= 0))")
    db.execute("INSERT INTO product VALUES (7, 'A', 4)")
    statement = """INSERT INTO product(code, stock) VALUES (?, ?)
                   ON CONFLICT(code) DO UPDATE SET stock = excluded.stock"""
    db.execute(statement, ("A", 9))
    db.execute(statement, ("A", 9))
    row = db.execute("SELECT id, code, stock FROM product").fetchone()
    assert row == (7, "A", 9)
    assert db.execute("SELECT count(*) FROM product").fetchone()[0] == 1
    print(row)

This statement stores a snapshot stock value. Replaying the same snapshot leaves the result unchanged, and the synthetic ID remains seven. A statement that adds excluded.stock to current stock would implement an increment instead. Replaying that increment would add twice unless the event itself had a deduplication identity. Snapshot idempotence is not event idempotence.

The table describes policy consequences, not universally preferred behavior. A correct inventory workflow may need an immutable movement ledger and a derived balance rather than direct snapshot replacement. Choose the representation that matches the source semantics before choosing the shortest SQL expression.

3. Reject stale revisions with an update condition

A conditional WHERE on DO UPDATE can turn the update into a no-op for an older revision. Compare a source-defined monotonic revision when available. Timestamps can serve a similar purpose only under a documented ordering rule; clock skew, equal timestamps, and late corrections can make simplistic timestamp comparisons unreliable.

import sqlite3

with sqlite3.connect(":memory:") as db:
    db.execute("CREATE TABLE reading (key TEXT PRIMARY KEY NOT NULL, revision INTEGER NOT NULL, value INTEGER NOT NULL CHECK(value >= 0))")
    statement = """INSERT INTO reading(key, revision, value) VALUES (?, ?, ?)
      ON CONFLICT(key) DO UPDATE SET
        revision = excluded.revision, value = excluded.value
      WHERE excluded.revision > reading.revision"""
    db.execute(statement, ("sensor-A", 2, 20))
    db.execute(statement, ("sensor-A", 1, 10))
    db.execute(statement, ("sensor-A", 3, 30))
    assert db.execute("SELECT revision, value FROM reading").fetchone() == (3, 30)
    try:
        db.execute(statement, ("sensor-A", 4, -1))
    except sqlite3.IntegrityError:
        pass
    else:
        raise AssertionError("CHECK failures must remain visible")
    assert db.execute("SELECT revision, value FROM reading").fetchone() == (3, 30)
    print(db.execute("SELECT * FROM reading").fetchall())

The final invalid value is not made valid by the UPSERT clause. The positive-value constraint remains part of the data contract. Handling uniqueness conflicts does not mean accepting every proposed row. The example catches the expected error only to demonstrate it; a production importer should record the rejected source record and an actionable reason.

4. Keep UPSERT separate from INSERT OR REPLACE

INSERT OR REPLACE uses SQLite's REPLACE conflict algorithm. On relevant uniqueness conflicts, that algorithm deletes conflicting rows before inserting a replacement. That is different from updating the existing row. The difference can affect synthetic IDs, relationships, triggers, and audit history, so treat REPLACE as a deliberate semantic choice rather than a spelling variant.

When child rows reference the existing identity, deleting the parent can interact with foreign-key actions. An ON DELETE CASCADE relationship may remove children; other relationships may block the operation. Foreign-key enforcement and transaction setup require their own checks. An import that appears to update a product can therefore have consequences well beyond one changed stock value.

UPSERT avoids that particular replacement pattern when its DO UPDATE updates the row in place, but it is not a general guarantee that all relationships are preserved. An update can still change a referenced key or violate another constraint. Keep identity fields stable where that is the intended policy, and test relationship behavior using a small representative database.

5. Define the transaction and failure boundary

Choose whether a batch is all-or-nothing or allows individual rejected records. For an atomic batch, begin a transaction, process the inputs, and roll back when the accepted contract fails. For partial acceptance, a savepoint or staging workflow can isolate failures while preserving a clear rejected-record report. The business decision should be explicit rather than an accidental result of exception handling.

In multirow inserts, UPSERT decisions are made per row. A constraint failure during DO UPDATE uses abort behavior for that statement; do not assume that a broad outer conflict clause makes every update harmless. Test the full statement and transaction together, especially when other unique columns or CHECK constraints are present.

Parameter binding protects data values from being interpreted as SQL syntax and avoids quoting errors. It does not validate the source's identity or quantity semantics. Keep table and column names fixed or selected through a trusted allowlist; placeholders represent values, not arbitrary SQL identifiers. Record the database schema and importer version with the batch report.

6. Audit outcomes and handle equal revisions deliberately

An equal revision with equal payload can be a harmless replay. An equal revision with different payload can indicate a source defect, a revision rule violation, or a nondeterministic transformation. A strict greater-than condition ignores both, so an importer may need a separate reconciliation check for contradictory equal revisions. Ignoring a conflict is not the same as explaining it.

Track input records, accepted identities, stale revisions, duplicate replays, and rejected constraints. Avoid inferring all of those categories from a single affected-row count, whose meaning may not distinguish every business outcome you care about. A staging table containing source row identity, revision, payload hash, and disposition can support a more useful audit.

The in-memory examples establish SQL behavior on synthetic fixtures; they provide no throughput benchmark or concurrency guarantee for a real deployment. Inspect sqlite3.sqlite_version, test on the deployed engine, and measure workload behavior when needed. A reliable UPSERT workflow combines an explicit identity, an explicit winning rule, visible constraint failures, and a transaction policy that matches the import's purpose.

Sources and further reading

• SQLite UPSERT documentation: conflict targets, excluded values, conditional updates, and statement behavior.
• SQLite ON CONFLICT documentation: conflict algorithms and the deletion semantics of REPLACE.
• SQLite CREATE TABLE documentation: uniqueness, NULL behavior, primary keys, and CHECK constraints. Import auditing and revision recommendations are design conclusions illustrated by the examples.