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?

Could you please specify what database platform you're working with? — Dan
– Dan, Commented Jan 25, 2012 at 16:59
Your spec says, "1.5 times the East or West cost (which ever is greater)" i.e. factor is 1.5 but your code uses the factor 1.8. — onedaywhen
– onedaywhen, Commented Jan 26, 2012 at 11:17

Conrad Frix · Accepted Answer · 2012-01-25 16:58:58Z

17

You need to use Case

 UPDATE ShippingCost

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

answered Jan 25, 2012 at 16:58

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Damon Over a year ago

Excellent! Works exactly the way I want. Thanks!

schtever · Accepted Answer · 2012-01-25 16:59:50Z

0

Use two update statements:

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

answered Jan 25, 2012 at 16:59

schtever

3,25019 silver badges25 bronze badges

1 Comment

schtever Over a year ago

Performance. If the table is large then the optimizer might not be smart enough to exploit an index if you use the CASE statement. Also, not sure how portable the CASE clause is. I know it wasn't supported in some older Oracle versions (you had to use DECODE). It's ANSI standard now, but you still have to make sure it works in the version of the DB you are running.

onedaywhen · Accepted Answer · 2012-01-26 11:15:59Z

0

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

answered Jan 26, 2012 at 11:15

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Comments

onedaywhen · Accepted Answer · 2012-01-26 11:18:51Z

0

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

Note: T-SQL dialect does not support GREATEST.

edited Jan 26, 2012 at 11:18

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

answered Jan 25, 2012 at 17:01

xphantom

133 bronze badges

2 Comments

Conrad Frix Over a year ago

MySQL and Oracle have GREATEST but I can't think of any Dialect that does Max(a,b)

xphantom Over a year ago

Yes, it's not MAX but GREATEST. Sorry for the mistake! MAX is another function that doesn't work in this case.

Collectives™ on Stack Overflow

SQL Conditional UPDATE Based on SELECT

4 Answers 4

1 Comment

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related