Re: multiple updates in one statement

From: Carl Kayser <kayser_c_at_bls.gov>
Date: Thu, 29 Jun 2006 09:26:14 -0400
Message-ID: <e80kdn$gcp$1@blsnews.bls.gov>

"Prasath" <prasath.rao_at_gmail.com> wrote in message news:1151586481.211964.178130_at_p79g2000cwp.googlegroups.com...
> Is it possible to merge the below 2 update statements into one single
> statement.
>
> 1. update test_table set my_col = 1 where my_flag = 'N';
> 2. update test_table set my_col = 2 where my_flag != 'N';
>

I believe so. The Sybase version follows and I believe that recent Oracle versions allow for this specific case syntax as well. (I've forgotten if this is new in Oracle versus "case expression when valuein then valueout ... end".) The (deprecated?) decode function might also be used.

update test_table
set my_col = case

                         when my_flag   = 'N'   then 1
                         when my_flag  <> 'N'  then 2
                     end

Note that there might be errors if my_flag is nullable and the other syntax is used. Received on Thu Jun 29 2006 - 08:26:14 CDT