SQL Conditional UPDATE Based on SELECT

Question

In SQL Server 2008, I have a set of data containing costs for East and West. I'm adding a new field for a customer for Canada which needs to be 1.5 times the East or West cost (which ever is greater). So I'm trying to come up with some sql I can execute. I've tried the following but have not had success:

 UPDATE ShippingCost

 SET

    IF EastCost>WestCost

       Canada= EastCost*1.8

    ELSE

       Canada= WestCost*1.8
    ENDIF

I'm sure there's 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