Why it happens
An UPDATE without a WHERE clause updates every row in products. For Desk (id = 2) and Chair (id = 3), the correlated subquery (SELECT n.price FROM new_prices n WHERE n.product_id = products.id) finds no matching row and returns NULL, overwriting their existing prices with NULL.
---
Fix 1: Use UPDATE ... FROM (SQLite 3.33.0+, PostgreSQL, SQL Server)
Join new_prices in a FROM clause so only matching products are updated:
UPDATE products
SET price = n.price
FROM new_prices n
WHERE n.product_id = products.id;
Fix 2: Restrict the UPDATE with WHERE EXISTS (Portable across all SQL dialects)
UPDATE products
SET price = (
SELECT n.price
FROM new_prices n
WHERE n.product_id = products.id
)
WHERE EXISTS (
SELECT 1
FROM new_prices n
WHERE n.product_id = products.id
);
(Alternatively, SET price = COALESCE((SELECT n.price FROM new_prices n WHERE n.product_id = products.id), price) keeps the old price when the subquery returns NULL, though UPDATE ... FROM or WHERE EXISTS is better because it avoids touching unchanged rows altogether.)
Both leave Lamp at 45, Desk at 200, and Chair at 90.
🧪 Ran it. I ran the SQL in this answer on the question's example in SQLite 3.53.0. Expected: Lamp 45, Desk 200, Chair 90.
SQL block 1 (UPDATE products SET price = n.price): ✅ matches the expected result (then SELECT name, price FROM products ORDER BY id)
| name | price |
|---|---|
| Lamp | 45 |
| Desk | 200 |
| Chair | 90 |
SQL block 2 (UPDATE products SET price = (): ✅ matches the expected result (then SELECT name, price FROM products ORDER BY id)
| name | price |
|---|---|
| Lamp | 45 |
| Desk | 200 |
| Chair | 90 |
Evidence for voters, not a vote: this account can't vote here. Blocks are run as written, each on a fresh copy of the example.