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

alex weber
Starting Member

4 Posts

Posted - 2011-06-04 : 01:13:37

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?
http://www.papalocksmith.com/

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?[

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-04 : 01:51:28

[code]
UPDATE P
SET Option_Stock = CASE WHEN Option_Stock > Basket_Qty THEN Option_Stock - Basket_Qty ELSE 0 END
FROM TB_Basket B INNER JOIN TB_Product_option P
ON P.Option_ID = B.product_Option_ID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-06-04 : 12:18:13

It is not suggested to even allow such thing if its a stock update.. basket quantity should never be less than available stock.

Where software development knowledge meets the reader

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-06 : 23:41:53

>> 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. <<

In the DDL , which you did not post, with a CHECK (onhand_qty > = 0) .

Please stop putting TB_ on table names; it violates ISO-11179 and it looks stupid. Never use the proprietary UPDATE..FROM.. syntax It does not work right or port. Why do you think that data elements change names from table to table?

UPDATE Product_Options
SET option_stock
= option_stock
COALESCE ((SELECT basket_qty
FROM Basket AS B
WHERE Product_Options.product_option_id = B.product_option_id
AND basket_qty <= option_stock),
0)

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources