SQL UPDATE With a Join: Match a Unique Source Record

The practice task

Apply fictional inventory counts to items already in a stock table. Leave an item with no new count unchanged and do not insert an unknown item merely because it appears in the count source.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE stock(sku TEXT PRIMARY KEY, quantity INTEGER);
CREATE TABLE counted(sku TEXT PRIMARY KEY, quantity INTEGER);
INSERT INTO stock VALUES ('A',3),('B',7);
INSERT INTO counted VALUES ('A',5),('C',9);
UPDATE stock SET quantity = counted.quantity
FROM counted WHERE counted.sku = stock.sku;

SELECT sku, quantity FROM stock ORDER BY sku;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

Item A matches the counted table and changes from three to five. B has no matching new count and stays at seven. C is not added because this is an update of existing stock rows, not an insert or an upsert.

The source primary key prevents two competing counted rows for the same SKU in this fixture. If a real source contains multiple observations, choose the authoritative one before the update, using a documented timestamp, revision or validation rule.

The example uses SQLite UPDATE FROM support in the tested version. Other SQL products express update joins differently. Verify both syntax and repeated-match behavior on the target engine instead of treating one dialect form as universal SQL.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Ensure the matching source key has the intended uniqueness.
• Inspect unmatched target and unmatched source records.
• Keep update, insert and upsert policies distinct.