Forum Moderators: open
am updating the products table, by adjusting the stock levels after a sale has been made.
when a sale is made items are inserted into a sales table, one row per item, such that if the same item is ordered twice it will have two rows in the sales table.
... the sale has been made the sales list has been inserted into the sales table and i'm trying to update the products table thus:
UPDATE products set stocklevel=stocklevel-1
WHERE products.productID IN (select productRef from salestable where thissalecolumn=123)
this works fine unless there are two items the same in the sales table (related to the same sale), in this case the products.stocklevel field is only reduced by 1 and not 2
i'm sure this is do-able
this is the end part of a stored procedure (sql server), perhaps i need to somehow loop through the records in some way but i'm unsure how to do that!)
UPDATE
Declare @SaleQty as SmallInt
Set @SaleQty=SELECT COUNT(*) FROM Orders WHERE OrderID= @OrderID <-- Your Input parameter
UPDATE products set stocklevel=stocklevel-@SaleQty
WHERE...
If I order 100 of the same item, are you saying that there will be 100 rows inserted? This sounds very inefficient.
Shouldn't you have a "quantity" field?Of course, but maybe a redesign at this point isnt a feasible option :) Maybe the typical order is 1 and max is 2 or 3.
Shouldn't you have a "quantity" field?
Of course, but maybe a redesign at this point isnt a feasible option Maybe the typical order is 1 and max is 2 or 3.
exactly right, the typical order is only 1 item of any given product - although they would probably order several different things.
infact usually only one item is available of a particular product at any one time - funnily enough it was redesigned about 4 years ago i took away the quantity field from the db and the corresponding ability for the customer to change quantities on their cart
- in this instance thinking they could make multiple orders killed the sale somewhat.
You would still need to loop over each order item.
select
..productRef,
..count(productRef) as count
from
..salestable
where
..thissalecolumn=123
group by
..productRef
Then loop through the above result and perform the update
UPDATE products set
..stocklevel=stocklevel - sqlOutput['count']
WHERE
..products.productID = sqlOutput['productRef']
Does this make sense or help? If not let me know and I will take another stab at it with you.
[edited by: Demaestro at 5:26 pm (utc) on Aug. 18, 2006]
UPDATE products P JOIN salestable S ON P.productID=S.productRef SET stocklevel=stocklevel+(SELECT COUNT(*) FROM salestable S WHERE S.productRef=P.productID AND S.thissalecolumn=123)
Edit: Of course that plus sign should be a minus!
[edited by: FalseDawn at 5:42 pm (utc) on Aug. 18, 2006]
SET stocklevel=stocklevel+(SELECT
Tested and working on MySQL - can't guarantee it'll work on MSSQL, but the principle is the same and I'm pretty sure it's an easy mod if it needs it.
Select COUNT(*) returns type number
No, select *allways* return type SET :)
I dont have SQL here but it fails in access
That's because Access is not a RDBMS in any sense of the word.
[edited by: FalseDawn at 7:06 pm (utc) on Aug. 18, 2006]
select count(*) returns a set... everywhere
What he would want is the [0][0] element of select count(*) from which would be an integer.
The first [0] is row 1 of the set, the 2nd [0] is the first arg in the defined row of the set
Sudo code of this is:
count = (select count(*) from blah)[0][0]
SELECT COUNT(*) returns a number. Period.
My original query was not quite right in the subquery correlation.
I have reformulated (MSSQL handles multi table updates differently than MySQL) and tested the query below - it works fine on MSSQL.
UPDATE products SET stocklevel=stocklevel+(SELECT COUNT(*) FROM salestable S2 WHERE S2.productRef=P.productID AND S2.thissalecolumn=123) FROM salestable S JOIN products P ON P.productID=S.productRef
I think the Set/Number issue is being handled by implicit conversion. Because SQL is based on set theory,all select statements return sets, but it seems some environments can figure out a set with only one element and automatically convert the result to the data type of that element.
