UPDATE / DELETE with Subqueries and JOINs

The data we'll use — stock and stock_in

This article covers a more advanced style of writing — updating and deleting using values from another table or an aggregate result.

Specifically, three patterns: updates whose values are computed by a correlated subquery (a subquery whose inner SELECT references columns from the outer row, so it's evaluated per row), join-style updates via UPDATE … FROM against another table, and DELETE statements with a subquery in the WHERE clause.

Before the exercises, take a look at the column definitions and sample data for the two tables — stock and stock_in.

① Run PRAGMA table_info(stock); to check column names, types, and the primary key of stock.

② Preview both tables with SELECT * FROM stock LIMIT 5; and SELECT * FROM stock_in;. Notice that stock_in contains a sku that doesn't exist on the stock side.

Use a correlated subquery to update with values from another table

Writing UPDATE table SET col = (SELECT ... WHERE subquery.key = table.key) evaluates the inner SELECT once per target row and writes the result into the column.

Because the inner SELECT references a column from the outer UPDATE target (stock.sku), it's called a correlated subquery.

The gotcha is this: for stock rows that have no matching row in stock_in, the inner SELECT returns NULL.

-- Without COALESCE: qty is overwritten with NULL for skus that have no match
UPDATE stock
SET qty = qty + (SELECT SUM(add_qty) FROM stock_in WHERE stock_in.sku = stock.sku);

If you run qty = qty + (SELECT ...) as-is, rows without an arrival get their qty overwritten with NULL.

To prevent this, either wrap the subquery in COALESCE(subquery, 0) to turn NULL into 0, or narrow the target with WHERE EXISTS (...) so you only touch rows that actually have an arrival.

-- First preview the post-update values with SELECT (stock isn't changed)
SELECT sku, qty,
  qty + (SELECT COALESCE(SUM(add_qty), 0) FROM stock_in WHERE stock_in.sku = stock.sku) AS new_qty
FROM stock;

-- Once you're happy, switch to UPDATE (COALESCE converts NULL to 0)
UPDATE stock
SET qty = qty + (SELECT COALESCE(SUM(add_qty), 0) FROM stock_in WHERE stock_in.sku = stock.sku);

Imagine the requirement: "reflect the quantities in the arrival table stock_in into the matching stock rows' qty." (Run it correctly and the explanation appears.)

① First run SELECT sku, qty FROM stock ORDER BY sku; to see the state before any update.

② Write the COALESCE version: a correlated-subquery update that adds the sum of stock_in.add_qty to stock.qty. For skus with no matching row in stock_in, treat NULL as 0 so their value stays unchanged. Run another SELECT afterward to confirm.

③ Then write the EXISTS version: an update that uses WHERE EXISTS (...) to limit the target to "rows that have an arrival." Run SELECT again and notice that A001 / A004 receive the same arrival a second time — they're double-counted.

UPDATE … FROM: join another table and update

Writing UPDATE table SET col = value FROM other_table WHERE join_condition lets you join the UPDATE target with the table named in FROM and update in one shot, referencing the join side's columns directly in SET.

-- UPDATE … FROM example: bump price by 10 only for items that have an arrival
UPDATE stock
SET price = price + 10
FROM stock_in
WHERE stock.sku = stock_in.sku;

-- Only the skus that joined (A001 / A004) see their price go up
SELECT sku, price FROM stock ORDER BY sku;

Rows in stock that have no matching row on the FROM side (stock_in) fail the join condition in WHERE and drop out of the update target automatically.

Unlike the correlated-subquery form, you don't need to guard against NULL separately.

Imagine the requirement: "do the same stock reflection from Exercise 1, but write it more compactly with UPDATE … FROM."

① Put stock as the update target and stock_in on the FROM side, join on sku, and write an UPDATE … FROM that adds add_qty to qty.

② End the script with SELECT sku, qty FROM stock ORDER BY sku; and confirm that only the joined skus (A001 / A004) have their stock increased.

DELETE with a subquery condition, and checking affected row count

You can put a subquery in the WHERE of DELETE, like DELETE FROM table WHERE col IN (SELECT ...) or WHERE EXISTS (SELECT ...).

This enables deletions that can't be expressed with a fixed value: "delete only rows that exist / don't exist in another table," or "delete rows whose aggregate result meets a condition."

Deletion is destructive and irreversible, so the standard practice in production is to first run the same WHERE inside a SELECT COUNT(*) to check the affected row count, then rewrite to DELETE.

After executing, verify with SELECT COUNT(*) FROM table; to confirm the remaining row count matches what you expected.

-- First check what would be deleted: items below the average price (stock isn't changed)
SELECT sku, name, price FROM stock
WHERE price < (SELECT AVG(price) FROM stock);

-- Once you're happy, switch to DELETE and check the remaining count
DELETE FROM stock
WHERE price < (SELECT AVG(price) FROM stock);
SELECT COUNT(*) AS remaining FROM stock;

Imagine the requirement: "delete from stock the discontinuation candidates — skus whose qty is 0 and have no arrival scheduled in stock_in." Since this is the article's final exercise, you'll perform a destructive deletion against the live table.

① Write a DELETE with a subquery condition that removes rows from stock where qty is 0 and sku does not exist in stock_in.

② After the delete, run SELECT COUNT(*) AS remaining FROM stock; to confirm how many rows are left. You can also use SELECT * FROM stock ORDER BY sku; to verify that A005 (Glue / qty 0 / no arrival) is gone.