Update with case-when

When we want to change a table so massively as shown in the previous post, we usually prefer to have a better control on the rows we are working with.

The case-when(-else) statement is what we are looking for.

Say that we want put as description for each row in the pastry table a different string accordingly with this rule:
All the names ending with "berry" should have a "With berries!" description.
All the other rows having a price less than 3 are a "Special offer".
Anything else would be a "A good choice".

This schema translates very well to this SQL statement:

update pastry
set description =
case
  when substr(name,-5) = 'berry' then 'With berries!'
  when price < 3 then 'Special offer'
  else 'A good choice'
end;

The "else" is optional, if we don't have a default we could simply skip it.
Notice that the destination field is fixed, while I can vary the condition to select the row subset at my wish.

There is a fleeble connection between this post and Head First SQL, chapter six, because I wrote it while reading this fun book.