SQLite Basics: Insert or Update Rows with UPSERT

October 19, 2026

UPSERT handles a uniqueness conflict as part of an INSERT. It is useful for imports, counters, and versioned records. These examples require SQLite 3.24.0 or later and use explicit conflict targets.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation executes SQL in a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. These toy databases disappear when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; this is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Refresh a product label

Save as refresh-product.sql:

CREATE TABLE products (code TEXT PRIMARY KEY NOT NULL, label TEXT NOT NULL);
INSERT INTO products VALUES ('P1', 'Old pen');
INSERT INTO products VALUES ('P1', 'Blue pen'), ('P2', 'Book')
ON CONFLICT(code) DO UPDATE SET label = excluded.label;
SELECT code, label FROM products ORDER BY code;

Run:

python3 run-sql.py refresh-product.sql

Output:

P1|Blue pen
P2|Book

excluded.label refers to the proposed insert value. P1 is updated and P2 is inserted; conflict handling is decided separately for each input row.

2. Add to an existing counter

Save as add-counter.sql:

CREATE TABLE visits (page TEXT PRIMARY KEY NOT NULL, hits INTEGER NOT NULL);
INSERT INTO visits VALUES ('/help', 5);
INSERT INTO visits VALUES ('/help', 2)
ON CONFLICT(page) DO UPDATE SET hits = visits.hits + excluded.hits;
SELECT page, hits FROM visits ORDER BY page;

Run:

python3 run-sql.py add-counter.sql

Output:

/help|7

The existing value and incoming increment are combined in one statement. This avoids a separate application-side read followed by a write. Validate increments if negative changes are forbidden.

3. Ignore stale revisions

Save as newer-revision.sql:

CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    revision INTEGER NOT NULL,
    body TEXT NOT NULL
);
INSERT INTO documents VALUES (1, 3, 'current');
INSERT INTO documents VALUES (1, 2, 'stale')
ON CONFLICT(id) DO UPDATE SET revision = excluded.revision, body = excluded.body
WHERE excluded.revision > documents.revision;
SELECT changes() AS changed;
INSERT INTO documents VALUES (1, 4, 'updated')
ON CONFLICT(id) DO UPDATE SET revision = excluded.revision, body = excluded.body
WHERE excluded.revision > documents.revision;
SELECT id, revision, body FROM documents ORDER BY id;

Run:

python3 run-sql.py newer-revision.sql

Output:

0
1|4|updated

The WHERE condition turns the stale update into a no-op. The newer revision succeeds. UPSERT handles uniqueness conflicts, not arbitrary NOT NULL, CHECK, or foreign-key failures. DO NOTHING is available when preserving the existing row is enough.

SQLite’s UPSERT reference describes conflict targets, excluded, and conditional updates.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py refresh-product.sql add-counter.sql newer-revision.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.

Last updated on October 19, 2026

SQLite Basics: Keep Related Rows Consistent with Foreign Keys SQLite Basics: Handle Missing Values with NULL and COALESCE