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

User · Answer

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

User · Answer

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

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

Examples related to sql-server-2005