SQLite joins source rows into UPDATE statements

An inventory adjustment often begins with rows in a different table. Individual sales describe events, while inventory describes the current quantity for each item. Updating the latter requires deriving a change from the former. SQLite 3.33.0 adds UPDATE FROM, which lets an update join its target table to a source relation. The August 14 release notes identify PostgreSQL compatibility as the syntax choice.

This is a useful extension because the source relation can express the calculation separately from the assignment. A subquery can aggregate sales by item, and the update can join that result to inventory. The resulting statement makes the relationship between the source data and the changed rows visible. It also places responsibility on the author to ensure that each target row has an unambiguous source value.

I value SQLite's ability to express relational work inside an application without introducing a database service. This addition makes a common cross-table operation more direct. Its value comes from expressing the set of changes clearly, including the cardinality of the join that determines those changes.

Aggregate the source before updating

The UPDATE documentation available at release uses a point-of-sale example. Sales accumulate in one table, and an update reduces inventory using the sum sold for each item. The source subquery groups the sales before joining them to the target. That grouping is essential to the example's meaning.

An illustrative schema makes the calculation concrete. These values demonstrate one adjustment and do not describe a complete inventory workflow.

CREATE TABLE inventory (
    item_id INTEGER PRIMARY KEY,
    quantity INTEGER NOT NULL
);

CREATE TABLE sales (
    item_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL
);

INSERT INTO inventory VALUES (1, 20), (2, 15);
INSERT INTO sales VALUES (1, 2), (1, 3), (2, 4);

UPDATE inventory
SET quantity = inventory.quantity - daily.amount
FROM (
    SELECT item_id, sum(quantity) AS amount
    FROM sales
    GROUP BY item_id
) AS daily
WHERE inventory.item_id = daily.item_id;

The source has one row for item 1 with an amount of 5, and one for item 2 with an amount of 4. The update therefore leaves quantities of 15 and 11 for those target rows. Each assignment uses a source amount that already represents all matching sales in the example. The update does not need to treat each sales row as a separate instruction to decrement inventory.

The source query's scope must also match the intended operation. In this example it includes every row in sales, so the update applies all of them. A real daily adjustment needs a source relation restricted to the relevant period or batch. Naming the subquery daily does not add that restriction. The predicates and grouping establish the actual meaning.

Running the same subtraction again would apply the same amounts again if the source rows remain unchanged. UPDATE FROM provides the join and assignment mechanism, but it does not record whether an application already processed a sales batch. That responsibility remains with the surrounding data model and transaction design. The example's arithmetic is deliberately limited to one execution.

A join does not imply repeated assignment

The release-date documentation gives an explicit rule for multiple matches. If the join produces several output rows for a target row, SQLite uses only one of those rows to update the target. Its choice is arbitrary and can change between executions or releases. An update with several matches therefore cannot be interpreted as a loop that applies every source row in turn.

Removing the aggregation from the example would change its meaning substantially. Item 1 has two sales rows, but a join to those rows does not promise two successive subtractions. One matching output row supplies the update's values. The documented behavior makes such a statement unsuitable for expressing the intended total reduction.

Grouping by the join key addresses this in the example because the source produces one amount per item. The target's primary key identifies one inventory row per item as well. Both sides of the relationship are visible in the SQL. That is a stronger explanation of correctness than assuming the update processes source rows in their apparent order.

The same issue applies outside arithmetic. A configuration update joined against several candidate values needs a rule that determines which value belongs to the target. Merely having a matching source row is insufficient when more than one candidate qualifies. The source relation can express a grouping or selection policy before the update uses it.

I prefer to make that policy explicit in the source query. The arbitrary-match rule is a defined behavior of the extension, so a statement can execute successfully while still expressing the wrong application operation. The database can perform the requested join without knowing that the author intended a different selection rule.

The target is named differently across engines

SQLite's syntax follows the PostgreSQL form in which the update target is not repeated in the FROM clause. The target is the table after UPDATE, and the source relation follows FROM. The documentation permits a self-join, but that requires the source instance of the target table to have a different alias. This prevents the two roles from being confused in the statement.

The choice is relevant because UPDATE FROM is an extension outside the SQL standards described by the release documentation. Other engines implement the idea with different syntax. A familiar operation name does not guarantee that a statement can be copied between them unchanged. SQLite's compatibility target narrows which familiar form to expect.

SQL Server's form repeats the target table in FROM, according to the same documentation. MySQL instead places the join specification between UPDATE and SET, without a FROM clause for this purpose. Its syntax also permits updating multiple joined tables. SQLite's addition should therefore be understood through its own single-target form and PostgreSQL-oriented syntax.

This distinction helps when translating an existing statement. The target relation must be identified before changing punctuation or moving a join clause. A query written for a multi-target update may describe an operation with different capabilities from SQLite's form. The shared idea of joining data into an update does not erase that difference.

The source relation is the main design decision

The historical documentation separately describes optional ORDER BY and LIMIT clauses for builds that enable SQLITE_ENABLE_UPDATE_DELETE_LIMIT. Those clauses concern the target rows selected for an update. They do not supply a rule for resolving several source matches to the same target. Conflating those purposes would leave the ambiguity in the joined source intact.

Even with that optional support, the documented ordering determines which rows fall within the limit. It does not determine the order in which SQLite modifies those rows. This distinction is useful when an application tries to infer procedural behavior from a statement that describes a set. Selecting a limited set of targets is different from specifying a sequence of assignments.

The documentation also excludes LIMIT and ORDER BY on updates inside triggers, regardless of the compilation option. A statement's availability therefore depends on both its form and the context in which it appears. The joined inventory example uses neither optional clause, keeping its meaning dependent on the source aggregation and target predicate alone.

The new clause makes it convenient to isolate the relation that supplies update values. That relation can be inspected as a select before it becomes the source of assignments. For the inventory example, the important properties are its grouping key and the amount calculated for each group. The update then relates that key to the inventory row it changes.

This separation can also clarify review. A reader can ask whether the source represents the correct batch of sales, and then whether the target join selects the intended inventory items. Those questions concern different parts of the statement. Combining them in one SQL operation does not require hiding either part behind application iteration.

Rows in the target without a match do not satisfy the example's WHERE condition. Their quantities therefore remain outside this adjustment. The example has no separate policy for missing inventory entries that appear in sales. Such entries need whatever validation the application requires, because an update of existing target rows does not create the missing rows.

The release includes other changes alongside the new statement form. It raises the maximum database-file size to 281 TB and extends PRAGMA integrity_check so it can check a single table and its indexes. Those are separate capabilities in the release notes. Neither changes the requirement for an update source to express the intended relationship between rows.

The inventory calculation illustrates why that requirement is the central issue for this feature. A single source amount per item gives the target assignment a definite meaning. With that relation established, SQLite can express the adjustment in one joined update. Without it, a syntactically valid join can leave the application dependent on the documented arbitrary choice among matching source rows.