SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 if statement in update procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alex weber
Starting Member

4 Posts

Posted - 06/04/2011 :  01:13:37  Show Profile  Reply with Quote
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?[

Edited by - alex weber on 06/23/2011 02:10:58

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 06/04/2011 :  01:51:28  Show Profile  Reply with Quote

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



KH
Time is always against us

Go to Top of Page

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 06/04/2011 :  12:18:13  Show Profile  Reply with Quote
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
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 06/06/2011 :  23:41:53  Show Profile  Visit jcelko's Homepage  Reply with Quote
>> 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000