CODEWITHSUNDEEP
sqlsql-serversql-server-2008selectsql-update
Damon
Damon

Reputation: 716

SQL Conditional UPDATE Based on SELECT

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?

Upvotes: 6

Views: 22323

Answers (4)

xphantom
xphantom

Reputation: 13

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

Note: T-SQL dialect does not support GREATEST.

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57093

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

Upvotes: 0

schtever
schtever

Reputation: 3250

Use two update statements:

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

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52675

You need to use Case

 UPDATE ShippingCost

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

Upvotes: 17

Related Questions