SQLite / Basic: the embedded engine and correct data / Lesson 6

Safe mutations, RETURNING, and UPSERT

Express a permitted state transition, verify its affected rows, and distinguish ordinary updates from conflict-resolution behavior that can delete or ignore data.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Use explicit INSERT columns and guarded UPDATE.
Interpret affected results and transaction outcome.
Choose UPSERT rather than assuming REPLACE means update.

State the allowed change before writing it

A mutation should describe a valid transition: reduce product1 stock by two only if at least two units remain. State the target key, eligibility predicate, expected affected count, and meaning of no match. UPDATE or DELETE without an appropriate predicate can affect an entire table. A successful SQL statement does not prove the target set matched the business request.

A SELECT preview helps a human review scope but does not reserve those rows against another connection. Put correctness in the modifying predicate, constraints, and any required transaction strategy. Keep domain validation such as positive quantity separate from the stock-availability test. A negative decrement would increase stock even though its arithmetic looks syntactically legitimate.

Update the stored value in one operation

The first script updates quantity=quantity-2 with quantity>=2 and returns the changed row. It avoids calculating a replacement in application memory from an earlier stale read. A zero-row outcome is not automatically an exception; the API must decide whether it means missing product, insufficient stock, or a deliberately ignored request. Capture the result before unrelated statements obscure the context.

RETURNING is available from SQLite3.35.0 and exposes affected values. It does not by itself prove a surrounding transaction committed, and multirow RETURNING output has no promised order. Consume or finalize the statement appropriately before completing the transaction. The script has one qualifying row, so its expected output does not depend on an arbitrary multirow sequence.

sql A guarded arithmetic stock change

CREATE TABLE stock(id INTEGER PRIMARY KEY,quantity INTEGER NOT NULL CHECK(quantity>=0));
INSERT INTO stock VALUES(1,5);
UPDATE stock SET quantity=quantity-2 WHERE id=1 AND quantity>=2
RETURNING id,quantity;
SELECT changes() AS changed;

Expected output

id|quantity
1|3

changed
1

The single row changes from5 to3, and changes() reports one affected row for the update.

Keep column lists and identity evidence explicit

List INSERT destination columns so unrelated schema changes do not silently reinterpret positional values. An omitted value can use a declared default; explicit NULL remains a different input subject to constraints. For an INTEGER PRIMARY KEY, the engine can choose a rowid when omitted. Retrieve generated values through the appropriate driver result or RETURNING rather than guessing the next number from a maximum.

last_insert_rowid() is connection-related state and not a universal identity for arbitrary prior work. Shared connection use and triggers complicate casual assumptions. A generated identifier is not evidence of a committed business operation and gaps do not measure missing records reliably. Keep request identity, row identity, and transaction confirmation as separate concepts.

Choose an explicit uniqueness-conflict action

UPSERT responds to a uniqueness conflict with a specified update or no-op. The second script adds an incoming quantity to an existing SKU through excluded.quantity, the proposed value. UPSERT began in SQLite3.24.0, with later syntax enhancements; the shown form is tested on3.45.1. It does not catch every CHECK, NOT NULL, or foreign-key violation as if all were duplicate keys.

INSERT OR REPLACE has different semantics: it can delete conflicting rows before insertion and interact with foreign keys, triggers, and identity. It is not shorthand for preserving the old row and updating selected fields. Likewise, OR IGNORE can skip work a caller expected to happen. Choose rejection, reconciliation, update, or no-op from the business contract rather than whichever syntax suppresses an error.

sql A deliberate add-on-conflict policy

CREATE TABLE stock(sku TEXT PRIMARY KEY NOT NULL,quantity INTEGER NOT NULL);
INSERT INTO stock(sku,quantity) VALUES('A',5);
INSERT INTO stock(sku,quantity) VALUES('A',2)
ON CONFLICT(sku) DO UPDATE SET quantity=stock.quantity+excluded.quantity
RETURNING sku,quantity;

Expected output

sku|quantity
A|7

The proposed quantity2 is added to the existing5, yielding7 for the same key. This is not a delete-and-reinsert policy.

Review deletion and full business completion

DELETE obeys constraints and can invoke cascading actions. Review retention and child relationships before designing removal; a soft-deletion flag is also a domain policy that affects every relevant query. SQLite does not provide another engine's TRUNCATE syntax as a universal replacement. Batched deletion needs a stable key selection and a progress rule rather than an unordered row-count assumption.

Verify accepted and rejected cases, including zero rows, duplicate requests, invalid quantities, and failure between related mutations. A complete operation may need several statements in one explicit transaction, developed next. Returning rows, receiving a driver success result, committing, and delivering confirmation to the caller are separate events. Reliable application design defines how uncertainty at those boundaries is handled.

Common errors and fixes

Every row was updated.

Why it happens: The target predicate was missing or broader than intended.
How to fix it: Use an approved recovery plan and correct the scope in isolation.
Prevent it next time: Review target keys and expected affected counts.

A duplicate-key workaround removed dependent data.

Why it happens: REPLACE was mistaken for an ordinary update.
How to fix it: Use a deliberate UPSERT or rejection policy and inspect relationships.
Prevent it next time: Test conflict behavior with child rows.

Stock increased during a sale.

Why it happens: A negative quantity passed into subtraction.
How to fix it: Reject nonpositive requested quantities and retain storage constraints.
Prevent it next time: Test zero, negative, excessive, and normal requests.

Check your understanding

1. Starting stock is5 and two callers each request4. Why is reading5 then setting1 unsafe?

Show worked solution

Both can read the same old value and both report success while selling eight units. Use a guarded arithmetic update and inspect its outcome within the complete transaction design. A later request should not succeed merely because its earlier read looked sufficient.

2. When is DO NOTHING a valid duplicate policy?