Use an UPSERT that updates one column

By Batu · English technical notes

Also published in our primary archive.

An UPSERT combines an attempted insert with a conflict-specific alternative. Here, key is the primary key and the table begins with a theme row holding both a value and its provenance. The next statement proposes another row with the same key, so its ON CONFLICT(key) DO UPDATE action is selected.

The update list is deliberately narrow: SET value = excluded.value changes only value. In an UPSERT update clause, excluded.value is the proposed value from the attempted insert. Because source is not in the update list, it remains "default", rather than becoming "user". The assertion checks the complete selected row; rowcount alone cannot establish which columns kept their prior values. SQLite explains the excluded row in its UPSERT documentation, and Python documents the parameterized Cursor.execute() interface used here.

SQLite UPSERT syntax requires SQLite 3.24.0 or later. The Python version does not by itself guarantee the SQLite runtime version bundled by a given interpreter, so applications that depend on UPSERT should check their supported runtime. The example uses no newer Python-specific API and does not define a broader merge policy or resolve concurrent business decisions. AI assistance was used to draft this article.

Example

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT, source TEXT)")
con.execute("INSERT INTO settings VALUES (?, ?, ?)", ("theme", "light", "default"))

result = con.execute(
    """
    INSERT INTO settings(key, value, source) VALUES (?, ?, ?)
    ON CONFLICT(key) DO UPDATE SET value = excluded.value
    """,
    ("theme", "dark", "user"),
)
row = con.execute("SELECT key, value, source FROM settings").fetchone()

assert result.rowcount == 1
assert row == ("theme", "dark", "default")

print(row)

Expected output:

('theme', 'dark', 'default')