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.
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
>> 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
if statement in update procedure
Posted - 06/04/2011 : 01:13:37
