SQLite Basics: Update Rows from a Matched Source with UPDATE FROM

An imported price list, a set of usage events, or a revised device configuration can drive an UPDATE. SQLite’s UPDATE FROM joins those source rows to the table being changed. The important design rule is to produce no more than one source match for each target row.

UPDATE FROM requires SQLite 3.33.0 or later. If several source rows match one target row, SQLite chooses one arbitrarily; adding a sort elsewhere is not a reliable way to choose the winner. Enforce uniqueness or reduce the source to one row first. UPDATE FROM reference.

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. All three SQL files below were executed through the Python runner, and the output shown is observed output. The standalone SQLite CLI is not used.

Prepare a scratch directory

You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages or SQLite CLI are needed. Keep the same terminal open and save files as UTF-8. Stop if setup fails before saving any example files.

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

Save as run-sql.py. Each invocation uses a fresh in-memory database, prints pipe-separated rows, and closes the database afterward. This teaching runner does not escape embedded pipes or newlines. Put each statement’s ending semicolon at the end of a line, and do not put two statements on one line.

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()

The examples are independent. All writes affect disposable toy tables, not an existing database file.

1. Apply only changed prices from a unique source

Save as apply-price-updates.sql:

CREATE TABLE products (
    code TEXT PRIMARY KEY NOT NULL,
    cents INTEGER NOT NULL CHECK (cents >= 0)
);
CREATE TABLE price_updates (
    code TEXT PRIMARY KEY NOT NULL,
    cents INTEGER NOT NULL CHECK (cents >= 0)
);
INSERT INTO products VALUES ('A1', 100), ('B2', 250), ('C3', 400);
INSERT INTO price_updates VALUES ('A1', 125), ('B2', 250), ('Z9', 900);
SELECT p.code, p.cents AS old_cents, u.cents AS new_cents
FROM products AS p JOIN price_updates AS u ON u.code = p.code
WHERE p.cents IS NOT u.cents
ORDER BY p.code;
UPDATE products AS p
SET cents = u.cents
FROM price_updates AS u
WHERE p.code = u.code AND p.cents IS NOT u.cents;
SELECT 'updated rows', changes();
SELECT code, cents FROM products ORDER BY code;

Run:

python3 run-sql.py apply-price-updates.sql

Observed output:

A1|100|125
updated rows|1
A1|125
B2|250
C3|400

The preview and UPDATE use the same match and change conditions. A1 changes, B2 already has the desired price, and C3 has no source row. Z9 is not inserted because this is an UPDATE, not an UPSERT.

Both code columns are required unique keys, so every target has at most one source match. The SET target is the unqualified column name cents; aliases distinguish the values read from the two tables. SQLite does not require the target table to appear again in FROM. UPDATE syntax.

2. Combine multiple usage events before updating

Save as apply-grouped-usage.sql:

CREATE TABLE budgets (
    project TEXT PRIMARY KEY NOT NULL,
    remaining_minutes INTEGER NOT NULL CHECK (remaining_minutes >= 0)
);
CREATE TABLE usage_events (
    id INTEGER PRIMARY KEY,
    project TEXT NOT NULL,
    minutes INTEGER NOT NULL CHECK (minutes > 0)
);
INSERT INTO budgets VALUES ('docs', 90), ('ops', 60), ('site', 120);
INSERT INTO usage_events VALUES
    (1, 'docs', 20), (2, 'docs', 15), (3, 'site', 30);
BEGIN;
UPDATE budgets AS b
SET remaining_minutes = b.remaining_minutes - u.used_minutes
FROM (
    SELECT project, SUM(minutes) AS used_minutes
    FROM usage_events
    GROUP BY project
) AS u
WHERE b.project = u.project;
SELECT project, remaining_minutes FROM budgets ORDER BY project;
ROLLBACK;
SELECT 'restored', project, remaining_minutes
FROM budgets ORDER BY project;

Run:

python3 run-sql.py apply-grouped-usage.sql

Observed output:

docs|55
ops|60
site|90
restored|docs|90
restored|ops|60
restored|site|120

The grouped source supplies one 35-minute adjustment for docs. Joining directly to the two docs events would not apply two successive deductions; it would leave competing source matches. Ops remains unchanged because it has no events.

ROLLBACK restores the budget after the preview. If an actual import commits the deduction, it also needs a way to record which events were consumed. Otherwise, rerunning the same successful batch would deduct the minutes again. UPDATE FROM does not provide idempotency by itself.

The CHECK constraint is a final guard against a negative result. Application code must still report an over-budget batch and roll back related work, rather than treating a database error as success. CHECK constraints.

3. Choose the newest revision with an explicit tie-breaker

Save as apply-latest-device-revision.sql:

CREATE TABLE devices (
    id INTEGER PRIMARY KEY,
    label TEXT NOT NULL
);
CREATE TABLE revisions (
    event_id INTEGER PRIMARY KEY,
    device_id INTEGER NOT NULL,
    revision INTEGER NOT NULL,
    label TEXT NOT NULL
);
INSERT INTO devices VALUES (1, 'Old sensor'), (2, 'Unchanged');
INSERT INTO revisions VALUES
    (10, 1, 2, 'Sensor A'),
    (11, 1, 3, 'Sensor B'),
    (12, 1, 3, 'Sensor C');
WITH ranked AS (
    SELECT device_id, label,
           ROW_NUMBER() OVER (
               PARTITION BY device_id
               ORDER BY revision DESC, event_id DESC
           ) AS position
    FROM revisions
)
UPDATE devices AS d
SET label = r.label
FROM ranked AS r
WHERE d.id = r.device_id AND r.position = 1;
SELECT id, label FROM devices ORDER BY id;

Run:

python3 run-sql.py apply-latest-device-revision.sql

Observed output:

1|Sensor C
2|Unchanged

Revision 3 wins over revision 2. The two revision-3 events are resolved by the larger event_id, an explicit rule for this toy import. The position filter removes every other match before the update. Device 2 has no revision and keeps its label.

Pick a tie-breaker that reflects the real contract, or reject ties if they represent corrupt input. A greater event ID is not universally proof of a newer event. The window’s ordering determines rank; the final SELECT has its own ordering for readable output. Window function ordering.

Before applying this pattern to real data, check the target keys, source cardinality, unmatched source rows, and expected update count. Keep related validation and changes in a suitable transaction. These examples use a single connection and do not test concurrent writers.

Clean up

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

rm -- run-sql.py apply-price-updates.sql apply-grouped-usage.sql apply-latest-device-revision.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.