SQL
Batch Update from Another Table
Efficiently update a column in one table with values derived from or conditioned by data in a second related table using an UPDATE with a JOIN clause.
UPDATE products p
SET stock_quantity = p.stock_quantity - oi.ordered_quantity
FROM order_items oi
WHERE p.id = oi.product_id AND oi.order_status = 'completed';
How it works: This powerful query performs a batch update on the `products` table based on data in the `order_items` table. It's used to decrement `stock_quantity` for products that were part of 'completed' orders. The `FROM` clause (common in PostgreSQL/SQL Server) or a `JOIN` clause (common in MySQL) allows you to link tables in an `UPDATE` statement to apply changes conditionally and efficiently across many records.