if statement in update procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

if statement in update procedure

Author

Topic

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2011-03-03 : 08:37:06

Hi guys,
I am trying to update a stock level on a database, at the moment it updates the database no problems, but I seem to be having a problem that the stock level can reach negative numbers.

How can set this up so that if the stock level is below zero set it to zero?

UPDATE TB_Product_option
SET Option_Stock = Option_Stock - Basket_Qty
FROM TB_Basket
WHERE TB_product_Option.Option_ID = TB_Basket.product_Option_ID

Should I use an if statement to calculate this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-03 : 08:44:39

UPDATE TB_Product_option
SET Option_Stock = case when Option_Stock - Basket_Qty<0 then 0 else Option_Stock - Basket_Qty end
FROM TB_Basket
WHERE TB_product_Option.Option_ID = TB_Basket.product_Option_ID

Madhivanan

Failing to plan is Planning to fail

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2011-03-03 : 08:58:57

thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources