Using CASE in UPDATE statements

I believe you know the general way to write update statements. But let's revise and take a look at Figure 1.

If we want to update salary of the persons with salary over $50000, the query will look like this:

UPDATE Persons
SET salary = salary*2
WHERE salary > 50000;

We have just doubled salary for these persons!

Let's see a more interesting example. Suppose we want to increase salary for persons with salary over $60000 by 3 percent, whereas all others receive a 5% raise. We want to do that by executing only one(!) query.

Try to write the query by yourself before you look at the solution.

We can do it using case statement:

UPDATE Persons
SET salary = CASE
  WHEN salary <= 60000 THEN salary*1.05
  ELSE salary*1.03
END

The general form of the case statement:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  WHEN conditionN THEN resultN
  ELSE result0
END

The query returns result of the first condition that is satisfied. If none of the conditions is satisfied, the query returns result0.

This construction can be executed in MySQL. It's rather useful.

---

I used this great book to write the article.

For any questions feel free to write comments or contact me by email.

---