Updates, upserts, and affected rows

The idea

An UPDATE changes every row satisfying its WHERE predicate. Omitting the predicate affects the entire table, so inspect the intended candidate set before writing a broad mutation. A zero-row update can be meaningful failure, such as a reservation whose remaining capacity was insufficient. Checking the outcome is part of the application contract rather than assuming every executed statement changed a record.

SQLite’s ON CONFLICT ... DO UPDATE syntax can express an upsert against a specific uniqueness rule. The excluded qualifier refers to the proposed row’s values. This differs from casually replacing a row, which may have deletion and relationship consequences. Define whether repeated input should overwrite a total or increment it, and write the update expression to match that policy. A transaction still governs atomicity across several required statements.

CREATE TABLE counts(label TEXT PRIMARY KEY,total INTEGER NOT NULL);
INSERT INTO counts VALUES('bug',2);
INSERT INTO counts VALUES('bug',3) ON CONFLICT(label) DO UPDATE SET total=counts.total+excluded.total;
SELECT label,total FROM counts;

label | total
bug | 5

The conflict identifies the existing label, and the update adds the proposed three to its current two.