In SQL, how do you apply 100,000 per-row updates from a staging table in one statement?

## The problem this solves A batch of per-row changes — new prices for 100,000 SKUs, new statuses for a list of orders — cannot be expressed as a single predicate, because each row gets a *different* value. The naive response is a loop: one `UPDATE ... WHERE sku = ?` per change. The set-based response is to put the changes in a table and let SQL join to them, so 100,000 changes become one statement. ## Step 1: get the batch into a table Load the changes into a staging table with just the join key and the new values: ```sql CREATE TABLE price_staging (sku VARCHAR(32) PRIMARY KEY, price DECIMAL(10,2) NOT NULL); ``` Load it with multi-row `INSERT`s (or your engine's bulk loader). The primary key on `sku` does two jobs: it makes the join efficient, and it prevents the duplicate-key problem described below from ever arising. ## Step 2: the portable apply statement Standard SQL's `UPDATE` has no `FROM` clause and no `JOIN`. What it does have is a scalar subquery, which may be correlated to the row being updated: ```sql UPDATE products SET price = (SELECT s.price FROM price_staging s WHERE s.sku = products.sku) WHERE EXISTS (SELECT 1 FROM price_staging s WHERE s.sku = products.sku); ``` Read the two halves separately. The `SET` clause computes the new value per target row. The `WHERE` clause decides *which* target rows the statement touches — and this is the half people omit. ## The NULL-everything bug Drop the `WHERE EXISTS` and the statement means "for every row in `products`, set price to the staging price for that sku". For a product with no staging row, the scalar subquery returns no rows, and a scalar subquery that returns no rows evaluates to `NULL`. So every product not in the batch gets `price = NULL`. The statement reports a huge affected-row count, raises no error, and silently destroys the column. This is the single most common failure of the pattern and a favourite interview trap: the guard is not an optimisation, it is what makes the statement correct. The same guard is also what keeps the statement cheap: it restricts the update to the rows that actually change, instead of rewriting every row of the table. ## Duplicates in the staging table If two staging rows share a `sku`, the scalar subquery returns two rows where one value is required. Standard SQL raises a cardinality error — the statement fails loudly, which is the behaviour you want. Dialect join forms are less kind: `UPDATE ... FROM` in PostgreSQL and SQL Server, given multiple matches, applies one arbitrarily chosen match, so the outcome depends on the plan and can differ between runs. Either way the fix is upstream: give the staging table a primary key on the join column, or dedupe it (`GROUP BY` with an aggregate, or a ranked filter) before applying. ## Dialect spellings Every major engine offers something shorter than the correlated form, because it reads better and only touches the staging table once: ```sql -- PostgreSQL / SQL Server family UPDATE products SET price = s.price FROM price_staging s WHERE s.sku = products.sku; ``` MySQL spells it as a multi-table update, `UPDATE products p JOIN price_staging s ON s.sku = p.sku SET p.price = s.price`. `MERGE INTO ... USING ... ON` is the standard's own statement for applying a batch, and it handles "update if present, insert if absent" in one pass; it exists in Oracle, SQL Server, DB2 and PostgreSQL 15 and later, while MySQL has no `MERGE`. If you must write one statement that runs everywhere, the correlated form with the `EXISTS` guard is the one that does. ## Rows in the batch that have no target row A staging batch usually contains a mix: some keys exist in the target, some do not. The update applies to the first group only. The second group needs an insert — either `MERGE`, or a second statement: ```sql INSERT INTO products (sku, price) SELECT s.sku, s.price FROM price_staging s WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.sku = s.sku); ``` Run the update first, then the insert, inside one transaction. ## Why not just build a giant CASE expression? A tempting alternative is `SET price = CASE sku WHEN 'A' THEN 1.00 WHEN 'B' THEN 2.00 ... END` with 100,000 branches and a matching `IN` list. It avoids the staging table, and it is a bad trade: the statement text is enormous, it must be parsed afresh every time because the literals change, it will hit statement-size limits, and it is unreadable. A table of values is the right way to hand a set of values to SQL. ## Checklist for the answer Bulk-load into staging; index the join key; dedupe it; write one set-based statement; never omit the `EXISTS` guard; add the insert half if the batch can contain new keys; wrap the pair in a transaction.