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.
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_optionSET Option_Stock = Option_Stock - Basket_QtyFROM TB_BasketWHERE TB_product_Option.Option_ID = TB_Basket.product_Option_IDShould I use an if statement to calculate this?[ |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-06-04 : 01:51:28
|
[code]UPDATE PSET Option_Stock = CASE WHEN Option_Stock > Basket_Qty THEN Option_Stock - Basket_Qty ELSE 0 ENDFROM TB_Basket B INNER JOIN TB_Product_option PON 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_OptionsSET 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 PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|