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.

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.

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

Answer 3

Thanks Guys... Will try these tomorrow and let you know.... Really appreciate your help...

Thomas, I am doing this because I am going to be counting these fields later... The fields are aging fields where if column a (date field) is between today and 30days, mark Y in coulmn_B (age 1-30) and so on..... Hope you understand the task in question here and let me know if there's a better way to do that...

I am always up for suggestions...

MAX