Using IF..ELSE in UPDATE (SQL server 2005 and/or A

问题:

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.

回答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.

回答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

回答3:

UPDATE products 
INNER JOIN zofertas 
    ON products.code=zofertas.codigo 
SET products.price=zofertas.precio 
IF products.price>zofertas.precio