CODEWITHSUNDEEP
sqlt-sql
I_G
I_G

Reputation: 413

update a column depending on the value of another column using case

I want to update a column depending on the value of another column. Something like this : 

update mt
set
 case 
  when mt.type = 1  (1=float 2=boolean 3=datetime 4=character)
  then mt.float_value = mt.value
 end

 case 
  when mt.type = 2  (1=float 2=boolean 3=datetime 4=character)
  then mt.boolean_value = mt.value
 end

case 
  when mt.type = 3  (1=float 2=boolean 3=datetime 4=character)
  then mt.datetime_value = mt.value
 end
...
from myTable mt

I want to update the column mt.float_value or mt.boolean_value or mt.datetime_value or mt.char_value depending on the value of the column mt.type

if the column mt.type = 1 then update mt.float_value with  mt.value
if the column mt.boolean_value = 2 then update mt.float_value with  mt.value

and so on... Is that possible and how can I perform this update? Thanks for help

Upvotes: 1

Views: 85

Answers (2)

Andomar
Andomar

Reputation: 238296

You could split it in three queries:

update  mt
set     float_value = convert(value, float)
where   type = 1

update  mt
set     bit_value = convert(value, bit)
where   type = 2

update  mt
set     datetime_value = convert(value, datetime)
where   type = 3

Upvotes: 2

paul
paul

Reputation: 22021

You could try this, but it will fail if any of the values cannot be converted to their destination column types:

update mt
set 
 mt.float_value = case 
  when mt.type = 1  then convert(mt.value, float) else mt.float_value
 end,

 mt.boolean_value = case 
  when mt.type = 2  then convert(mt.value, bit) else mt.boolean_value
 end,

mt.datetime_value = case 
  when mt.type = 3  then  convert(mt.value, datetime) else mt.datetime_value
 end
...
from myTable mt

Upvotes: 1

Related Questions