Update with case

dresmortis · #1 (**permalink**) 02-13-05, 04:06

I have a problem in an update, i need to update one coloum with a case condition that involves more than 1 case. The current sql statement i came up with goes like this.

update FMS_DATA
set valueDKKTEMP = CASE exchange.rates when 0 then fms_data.value * 1 else exchange.rates * fms_data.value end

FROM HFO_Account RIGHT OUTER JOIN
FMS_DATA ON HFO_Account.CHILD = FMS_DATA.ACCOUNT LEFT OUTER JOIN
exchange ON HFO_Account.EXCHTYPE = exchange.TYPE AND FMS_DATA.CURRENCY = exchange._NAME_ AND
FMS_DATA.TIME = exchange.TIME AND FMS_DATA.ANALYSIS = exchange.ANALYSIS

but i also need to check for the currency being = dkk like this

set valueDKKTEMP CASE fms_data.currency WHEN 'dkk' THEN fms_data.value * 1

how do i do this?

thx in advance

Littlefoot · #2 (**permalink**) 02-13-05, 13:06

Doesn't it work as an ordinary UPDATE statement? That is,

UPDATE your_table SET
column_1 = some_value,
column_2 = another_value
WHERE condition;

some_value would be your exchange.rates CASE, and another_value would then be your fms_data.currency CASE.

dresmortis · #3 (**permalink**) 02-13-05, 15:04

i only need to update the one coloum based on several conditions, the thing i want to do is

update FMS_DATA
set valueDKKTEMP = CASE exchange.rates when 0 then fms_data.value * 1 when fms_data.currency = 'dkk' THEN fms_data.value * 1
else exchange.rates * fms_data.value end

the above is incorrect i know, but this is what i need to do.

Because i must make sure the curency isn't 'dkk' and that exchange.rates isn't 0 and in all other cases exchange.rates * fms_data.value.

so actually i need 2 case to update 1 coloum.

thx

Littlefoot · #4 (**permalink**) 02-14-05, 01:21

I see ... sorry, didn't read your initial question carefully enough.

How about using the DECODE function? Maybe it'll help ...

Code:

UPDATE fms_data
   SET valuedkktemp =
          (SELECT DECODE (exchange.rates,
                          0, fms_data.value,
                             DECODE (fms_data.currency, 'dkk', fms_data.value, exchange.rates * fms_data.value)
             FROM exchange, fms_data
             WHERE ...)
WHERE ...;

And another suggestion ... watching this, perhaps you could use concatenation:

Code:

UPDATE fms_data
   SET valuedkktemp =
          (SELECT DECODE (to_char(exchange.rates) || fms_data.currency,
                          '0dkk', fms_data.value,
                                  exchange.rates * fms_data.value
                         )
             FROM exchange, fms_data
             WHERE ...)
WHERE ...;

And, then again, concatenated "rates and currency" could be used in the CASE statement ... couldn't they?

Internet Services

Web Development

Digital Marketing

Consumer Tech