SQL Conditional UPDATE Based on SELECT
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?
SQL Conditional UPDATE Based on SELECT
===

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?