UPDATE and IF Statements

[psychoflea]

Hi

Is there such a way that you can use an IF Statement within an UPDATE Statement.
Such as:

UPDATE Table 1
IF col1>= 500
SET col1 = col1*2.5
ELSE
SET col1 = col1*1.5

Thanks

 

[lewisp]

Try

[tt]UPDATE table1
SET col1 = Decode(Sign(499 - col1),
-1,col1 * 2.5,
col1 * 1.5);[/tt]

 

[SantaMufasa]

Psycho,

In addition to Lewis's excellent solution, this one also works:

UPDATE table1
set col1 = case when col1 >= 500 then col1 * 2.5 else col1 * 1.5 end;

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:57 (09Jan04) GMT, 13:57 (09Jan04) Mountain Time)