November 29, 2004 at 9:30 am
I have a stock control application which, when a product is shipped I want to decrement the current stock with the quantity of product which has just been shipped (in the same procedure).
My field names which count are as follows:
products.catalogid,
products.cStock
orders.orderid
oitems.orderid,
oitems.catalogId
oitems.odQuantity
I'm trying to write a procedure which when passed the orderid the following happends.
select orderid,catalogid, odQuantity from oitems where orderid = 123456
which returns:
orderid,catalogid,odQuantity
123456,160,1
123456,219,1
123456,222,1
123456,229,1
Now, with these results I'm trying to update the products.cStock field with this t-sql.
update products set cStock = (select cStock from products where catalogid = 160) - odQuantity
I'm trying to loop through the results of the select and do the same update on each of the rows returned from this select in the same procedure. I can't find anything in BOL unless I fix the values to say '160' for the top row, but I don't want to do this. I'm not really sure if this makes sense, but I'm trying to action an update using the results from a select.
Can anyone help ?
November 29, 2004 at 10:04 am
Hey Neil,
Hope this helps...........
DECLARE C1 CURSOR FOR select catalogid, odQuantity from oitems where orderid = 123456
OPEN C1
FETCH NEXT FROM C1 INTO @a, @b-2
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE products
set cStock = cstock - @b-2
WHERE catalogid = @a
FETCH NEXT FROM C1 INTO @a, @b-2
END
CLOSE C1
DEALLOCATE C1
MK
November 29, 2004 at 10:47 am
This works like a charm - thanks very much.
November 29, 2004 at 12:48 pm
Please don't use cursors when you can use a set based solution. This will run so much faster on the production system :
Update P set P.cStock = P.CStock - C.odQuantity from Products P inner join Orders O on P.catalogId = O.CatalogID WHERE OrderId = [Id of the new order].
December 2, 2004 at 12:48 am
Hi,
I also want to do something like this posting above but it does not work when I try to modify your statements above. I am currently using asp to do this which takes very long.
Right now I am doing it the following way:
rsSalessql = "SELECT SeqNumberTemp.ProductNameFull, (CASE WHEN OrderTotal > 0 THEN (SalesTotalTemp.OrderTotal * SeqNumberTemp.margin) ELSE SeqNumberTemp.margin END) AS Total FROM SalesTotalTemp RIGHT OUTER JOIN SeqNumberTemp ON SalesTotalTemp.ProductNameFull2 = SeqNumberTemp.ProductNameFull ORDER BY Total desc"
Set rsSales = SessionData.Execute(rsSalessql)
seq = 0
While not rsSales.EOF
seq = seq + 6
sql = "update Products SET salesseq = '" & seq &"' WHERE Manufacturer + ' ' + ProductName = '" & rsSales("ProductNameFull") & "'"
SessionData.Execute(sql)
rsSales.MoveNext
Wend
Can you please so that I can make this work faster just using sql server.
December 2, 2004 at 2:16 am
You'll need to do the update in only 1 query. The tricky part is that you seem to need to set a counter in the update query.
to do this you can do something like the following :
SELECT identity (int, 0, 6) as salesseq, SeqNumberTemp.ProductNameFull, (CASE WHEN OrderTotal > 0 THEN (SalesTotalTemp.OrderTotal * SeqNumberTemp.margin) ELSE SeqNumberTemp.margin END) AS Total into #SomeTempTable FROM SalesTotalTemp RIGHT OUTER JOIN SeqNumberTemp ON SalesTotalTemp.ProductNameFull2 = SeqNumberTemp.ProductNameFull ORDER BY Total desc
Then you can use the newly created temp table to inner join on the data you need to update and then drop the table (the "identity (int, 0, 6) as salesseq" part can only be done in a select into statement so you cannot keep the table for reuse and indexing).
It's an heavier load than normal on the server, but the cost will be much lower than to do 5000 queries (15 000 roundtrips) to make the update.
Or a better solution might reside in doing this logic in a trigger but that can be some tricky T-SQL too.
December 2, 2004 at 10:54 am
too much cursors and dynamic sql, Yoda sees. Leads to the darkside, they do.
Simple problem, it is -- do not process each row. A true jedi will have mastered aggregate functions!
UPDATE Products
SET Stock = Stock - (SELECT SUM(odQuantity) from oitems where OrderID = @ORderID AND oitems.ProductID = Products.ProductID)
Assuming the second part of the AND out, Yoda is. Surely you wish to relate the Product in the order to the products table? hmm? Or update all rows, you will!
December 3, 2004 at 9:18 pm
Thanks Remi for your reply but I have to update an existing table with sequence number for products and this is done only once a week. But it takes about 12 minutes if I do it using asp page and so I am thinking of completely doing it using a stored procedure or from query analyzer. Is there I can do the update faster and with less load on the server.
December 3, 2004 at 9:20 pm
Can you write the update query also for me after you create this temporary table.
December 4, 2004 at 12:51 pm
Hi Yoda or Remi, can you please write the update query also for me. I cannot seem to get it to work.
December 5, 2004 at 7:26 am
Can't do it before monday morning. But i promess I'll do it then.
December 6, 2004 at 6:51 am
I think something like this would work.
SELECT identity (int, 0, 6) as salesseq, SeqNumberTemp.ProductNameFull, (CASE WHEN OrderTotal > 0 THEN (SalesTotalTemp.OrderTotal * SeqNumberTemp.margin) ELSE SeqNumberTemp.margin END) AS Total into #SomeTempTable FROM SalesTotalTemp RIGHT OUTER JOIN SeqNumberTemp ON SalesTotalTemp.ProductNameFull2 = SeqNumberTemp.ProductNameFull ORDER BY Total desc
Update P set Salesseq = S.Salesseq from Products P inner join #SomeTempTable S on P.ProductNameFull = S.ProductNameFull
DROP TABLE #SomeTempTable
make sure you have an index on Products.ProductNameFull
Even better if you can try to use the product id to make the inner join instead of the ProductNameFull (which I imagine is a varchar field), it would improve performance a lot.
However the best solution would be something like this.
(this query runs in less than 2 seconds on a 10k rows table so for a weekly operation it would be more than fast enough
Select PkObjSQL, Name, (Select count(*) * 6 from dbo.ObjSQL O2 where O2.PkObjSQL <= OMain.PkObjSQL and O2.FkDB = OMain.FkDB) as CountALL from dbo.ObjSQL OMain where FKDB = 140 Order by PkObjSQL
)
but I'm not sure it's possible with your current setup (never tried to correlate to an aggregate before.
December 7, 2004 at 7:43 pm
Thanks this works!
December 7, 2004 at 9:45 pm
Just out of curiosity.. how long does it take now to perform the same operation compared to the old code?
December 10, 2004 at 11:56 am
hi, sorry for replying so late as I was tring to fix it myself. Your query did work but the results were different. I did it using my way and yours and then compared the 2 results and they were very different. Thanks for your help and I will try to give you a better idea of the problem and maybe then you can help me fix it.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply