SQL
Performing Multi-Column Conditional Updates
Learn to update multiple columns in a table based on complex conditions or derived values, ensuring precise and flexible data manipulation in a single SQL statement.
UPDATE products
SET
price = CASE
WHEN category = 'Electronics' AND stock < 10 THEN price * 1.10 -- 10% price increase
WHEN category = 'Books' AND stock < 5 THEN price * 0.95 -- 5% price decrease
ELSE price
END,
status = CASE
WHEN stock = 0 THEN 'Out of Stock'
WHEN stock < 5 THEN 'Low Stock'
ELSE 'In Stock'
END
WHERE id IN (101, 102, 103); -- Apply to specific products or use other WHERE conditions
How it works: This SQL snippet demonstrates how to perform a multi-column update using `CASE` statements for conditional logic. It updates both the `price` and `status` columns of `products` table based on different conditions related to `category` and `stock` levels. The `CASE` statement evaluates conditions sequentially and assigns a new value for each column, providing a powerful way to apply complex business logic in a single, atomic update operation.