Using a CASE Expression to Update a Column

The CASE expression allows a statement to return one of several possible results, depending on which of several condition tests evaluates to TRUE.

The following example shows how to use a CASE statement in an UPDATE statement to increase the unit price of certain items in the stock table:

UPDATE stock
   SET unit_price = CASE
      WHEN stock_num = 1 
      AND manu_code = "HRO"
      THEN unit_price = unit_price * 1.2
      WHEN stock_num = 1 
      AND manu_code = "SMT"
      THEN unit_price = unit_price * 1.1
      ELSE 0
      END

You must include at least one WHEN clause within the CASE expression; subsequent WHEN clauses and the ELSE clause are optional. If no WHEN condition evaluates to true, the resulting value is null.