Wednesday, September 21, 2005

Multiple conditions updates

PW says he would like to find a way of executing several UPDATE statements in one table using just one query.
In practice, he has several products from different companies, and he wants to update them in a compact way, without repeating the same query several times.

For example, PW wants to apply a different discount on books from IT, finance, and current affairs sections, with different amounts for each category.

One way is this one:

UPDATE books
SET discount =
CASE
WHEN section = 'IT' THEN 12
WHEN section = 'current affairs' THEN 10
WHEN section = 'finance' THEN 8
ELSE discount
END
WHERE id_publisher IN (5,6,8,11);

This query acts only on books from the requested publishers (see the WHERE clause) and among them updates the account column from the given sections.
The remaining sections the updated value is the same, thanks to CASE's function ELSE clause.

Since this operation runs in one query only, composing it could be a daunting task, harder than making separate queries for each condition, but sometimes there is a real efficiency gain, and it's good to know that there are alternatives available!

No comments: