Subject Re: [firebird-support] Case vs. update
Author Ann W. Harrison
semprolbat wrote:
> Does case work in update statements, and if not, is this feature
> planned?

Case is a value expression, not an operator.
> update
> tablea
> case
> when (a-b >= 0) then set a = a-b, b = 0
> else set a = 0, b = -(a-b)
> where
> (a > 0) and (b > 0)
> ;

update tablea
set a = case (a-b >=0) then a-b else 0,
set b = case (a-b >=0) then 0 else -(a-b)
where (a > 0) and (b > 0)

There is a messiness in the Firebird handling of values that will break
this particular example. The SQL standard specifies that the values
used as input to a set operation are the old values, even if new values
have been assigned to those fields. Firebird doesn't follow the
standard in that case, so the set statement that assigns a new value to
a will affect the results of the assignment to b.