Using IF..ELSE in UPDATE (SQL server 2005 and/or ACCESS 2007)

Question

I need to set a query like below:

UPDATE XXXXXX
IF column A = 1 then set column B = 'Y' 
ELSE IF column A = 2 then set column C = 'Y' 
ELSE IF column A = 3 then set column D = 'Y' 

and so on and so forth...

I am able to do this using multiple queries but was wondering, if I can do it in just 1 statement instead.

Fenny

Answer 1

this should work

update table_name
  set column_b = case
                  when column_a = 1 then 'Y'
                  else null
                 end,
  set column_c = case
                  when column_a = 2 then 'Y'
                  else null
                 end,
  set column_d = case
                  when column_a = 3 then 'Y'
                  else null
                 end
where
 conditions

the question is why would you want to do that...you may want to rethink the data model. you can replace null with whatever you want.

Phylissphyll

There are 2 extra "set" as I tried in SQL 2008. Thanks for the answer – Pernod

Answer 2

Yes you can use CASE

UPDATE table 
SET columnB = CASE fieldA 
        WHEN columnA=1 THEN 'x' 
        WHEN columnA=2 THEN 'y' 
        ELSE 'z' 
      END 
WHERE columnC = 1

Demimonde

can anybody tell what fieldA supposed to be here – Veii

@PriyankaArora that's what I'm wondering. – Kortneykoruna