SQL Conditional UPDATE Based on SELECT

Question

In SQL Server 2008, I have a dataset containing costs for East and West. I am adding a new field for a client for Canada, which should be 1.5 times higher than the cost of East or West (which is ever greater). Therefore, I am trying to come up with some sql that I can execute. I tried the following but did not succeed:

UPDATE ShippingCost SET IF EastCost>WestCost Canada= EastCost*1.8 ELSE Canada= WestCost*1.8 ENDIF 

I'm sure there is an easy way to do this? Any ideas?

Answer 1

You need to use case

  UPDATE ShippingCost SET Canada = CASE WHEN EastCost>WestCost THEN EastCost*1.8 ELSE WestCost*1.8 END 

Answer 2

Use two update statements:

 UPDATE SHIPPINGCOST SET Canada = EastCost*1.8 WHERE EastCost>WestCost UPDATE SHIPPINGCOST SET Canada = WestCost*1.8 WHERE EastCost<=WestCost 

Answer 3

  UPDATE ShippingCost SET Canada = GREATEST(EastCoast, WestCoast) * 1.8; 

Note. The T-SQL dialog does not support GREATEST .

Answer 4

 UPDATE ShippingCost SET Canada = 1.5 * CASE WHEN EastCost > WestCost THEN EastCost ELSE WestCost END;