UPDATE with CASE in SQL

A price review rarely changes one thing. Houseplants go up, discontinued stock is cleared at half price, and everything else stays put. Two UPDATE statements can do it, but they are easy to run in the wrong order or only half of. A CASE expression from Lesson 6.2 · Labeling rows with CASE in the SET clause does it in one statement:

update orders
set status = case
               when shipped_date is not null then 'shipped'
               when status = 'pending' then 'processing'
               else status
             end
where status <> 'cancelled';

select status, count(*) as orders
from orders
group by status
order by status;

ELSE keeps the rest

The CASE runs for every row the UPDATE touches. A row that matches no WHEN gets the ELSE value, and with no ELSE that value is NULL. So an update without a WHERE and without else price wipes the price of every product it was not meant to change. Write the ELSE even when a WHERE already limits the rows; it costs nothing and survives someone deleting the WHERE later.

The first matching WHEN wins

Branches are checked top to bottom, and a row stops at the first one that matches. A discontinued houseplant would get the clearance price here, because that branch comes first. Put the rule that should win on top.

Preview the new values

The same CASE works in a SELECT, so you can see every old and new price side by side before anything changes:

select product_id,
       price as old_price,
       case
         when discontinued then round(price * 0.5, 2)
         when category_id = 1 then round(price * 1.10, 2)
         else price
       end as new_price
from products
where category_id = 1 or discontinued
order by product_id