Categories
Coding

How to Update With Case in SQL

To update records with a CASE statement in SQL, you can use this syntax:

UPDATE table_name
SET column_name = CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
WHERE conditions;

Basic Use

UPDATE employees
SET salary = CASE
WHEN department = 'IT' THEN salary * 1.1
WHEN department = 'Sales' THEN salary * 1.05
ELSE salary
END;

This increases IT department salaries by 10% and Sales department salaries by 5%.

Multiple Column Updates

UPDATE products
SET
price = CASE
WHEN category = 'Electronics' THEN price * 0.9
WHEN category = 'Clothing' THEN price * 0.95
ELSE price
END,
stock_status = CASE
WHEN quantity > 100 THEN 'In Stock'
WHEN quantity > 0 THEN 'Low Stock'
ELSE 'Out of Stock'
END;

This updates both price and stock status based on different conditions.

Using Subqueries

UPDATE orders
SET shipping_cost = CASE
WHEN total_amount > 100 THEN 0
WHEN (SELECT count(*) FROM order_items WHERE order_id = orders.id) > 5 THEN 5
ELSE 10
END;

This sets shipping cost based on order total and number of items.

With JOIN

UPDATE customers c
JOIN orders o ON c.id = o.customer_id
SET c.status = CASE
WHEN o.total_amount > 1000 THEN 'VIP'
WHEN o.total_amount > 500 THEN 'Premium'
ELSE 'Regular'
END;

This updates customer status based on their order totals.

Using AND/OR in Conditions

UPDATE employees
SET bonus = CASE
WHEN department = 'Sales' AND sales_target_achieved = 1 THEN salary * 0.1
WHEN department = 'IT' OR department = 'HR' THEN salary * 0.05
ELSE 0
END;

This sets bonuses based on complex conditions.

Leave a Reply

Your email address will not be published. Required fields are marked *