Need help with an Update. - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help with an Update.

Author

Topic

Johnhamman
Starting Member

37 Posts

Posted - 2002-12-11 : 18:03:11

Hi I think this is a simple problem but I am not familur with sql.
I need to update a database to remove the items quantity after a user has purchased it. the select below grabs the products that have the quantity . But I dont know how to also update the V020U18NUC_AGC.Products database by looping thru and subtracting all OrderDetails.Quantity from Products.qty.
Can someone help?


Select pid,V020U18NUC_AGC.OrderDetails.Quantity as qty,V020U18NUC_AGC.OrderDetails.UnitCost as costperunit,(V020U18NUC_AGC.OrderDetails.UnitCost * V020U18NUC_AGC.OrderDetails.Quantity) as costperqty,
Name,Short as description,thumb 
From V020U18NUC_AGC.Products,V020U18NUC_AGC.OrderDetails
where V020U18NUC_AGC.OrderDetails.OrderID = @orderID
and pid = Productid

CactusJuice
Starting Member

46 Posts

Posted - 2002-12-11 : 18:49:29

Well first you should probably familUrize yourself with SQL :) Or have someone to translate for you. Otherwise the advice here isn't going to make much sense.

Build a list of Product ID #'s and after the "purchase" make sure @@Error is 0. Then run a DELETE on the OrderDetails table.

DELETE FROM OrderDetails WHERE ProductID IN (your_list_of_ID's)

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-12-11 : 20:55:39

1. Seems like you need an INNER JOIN here, instead of one part of the WHERE clause (Pid = ProductID), rather than the current syntax. Will work the same, but use current language.
2. Sounds like what you want is to keep stocking information current (after order, how many of this product left). The query: UPDATE Products SET QuantityInStock = QuantityInStock - (SELECT SUM(Quantity) FROM Orderdetails GROUP BY ProductID WHERE ProductId = Products.PID AND OrderID = @OrderId)

HTH,

P.S. In SQL Server we call it a table, not a database. A database is a file holding all tables, view, indexes, etc.

Sarah Berger MCSD

Johnhamman
Starting Member

37 Posts

Posted - 2002-12-11 : 22:29:02

Thanks so much Sarah, I think you got the Idea on what im looking to do. Did i call it a database! lol, Im not that bad off, but i guess the proof is in the pudding. or what ever that means.
Ok here is the code i pluged in:
UPDATE Products
SET Products.Qty = Products.Qty -
(SELECT SUM(OrderDetails.Quantity)
FROM OrderDetails ,Products
WHERE OrderDetails.ProductId = Products.PID AND OrderDetails.OrderID = @OrderId
GROUP BY V020U18NUC_AGC.OrderDetails.ProductID)

and i got this error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Edited by - johnhamman on 12/11/2002 22:43:21

mr_mist
Grunnio

1870 Posts

Posted - 2002-12-12 : 04:29:44

The message you are getting is because you are trying to take away a set of rows from a single value. This can't be done. You need to rewrite the update statement slightly, something like..

update products
SET Products.Qty = Products.Qty - rowstoupdate.ordertotal
from products inner join
(
SELECT products.pid, SUM(orderdetails.quantity) as ordertotal
from OrderDetails
inner join products on
OrderDetails.ProductId = Products.PID AND OrderDetails.OrderID = @OrderId
GROUP BY pid
) rowstoupdate on
rowstoupdate.pid = products.pid

But this would only do the updates for products ordered in @orderid order.

-------
Moo.

Edited by - mr_mist on 12/12/2002 04:30:37

Johnhamman
Starting Member

37 Posts

Posted - 2002-12-12 : 13:44:45

Perfect Thanks so much!
john

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources