Thread: Using case expressions in update set clause

I just want to clarify that the following will always behave the way I
think it's supposed to behave:-

     update  tableA
     set       date_field = case when date_field is null then some_date
                                          else date_field end;

If the current value of date_field for the current record is null then
set the date_field with some_date value otherwise keep the current
value of date_field.

Note: in the actual situation there'll be other fields that will
always be updated in addition to this one field that needs to be
conditionally updated.


Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
m: 0415 469 095
www.flashdev.org.au

"Chris Velevitch" <chris.velevitch@gmail.com> writes:

> I just want to clarify that the following will always behave the way I
> think it's supposed to behave:-
>
>      update  tableA
>      set       date_field = case when date_field is null then some_date
>                                           else date_field end;
>
> If the current value of date_field for the current record is null then
> set the date_field with some_date value otherwise keep the current
> value of date_field.

That's correct. In this case you could also do it with
 set date_field = coalesce(date_field, some_date)

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com