Avoid Looping / Cursors. Help with Statement.

Hi Folks,
I have two tables, one of which I want to update from another.
Essentiall I have a table of orders and a product table. I want to
subtract the qty sold in the orders table from the QtyInStock column in
the Products table, for every line in an order.
But I dont really want to loop through each line in the order, either
in application or with a cursor as something is telling me there must
be a neater solution!
Example Orders Table.
OrderID ProductID Qty
1, 104, 2
1, 199, 1
2, 100, 3
3, 858, 1
ProductID, QtyInStock
104, 3
199, 1
etc ...
As you can see I want to be able to run a query against OrderID 1,
and it to reduce the QtyInStock column by the correct amount for
products 104 and 199 as an example.
Can this be done with one statement, or will I have to loop ?
Thanks in Advance.
Craig.Hi
CREATE TABLE #Test1
(
OrderID int,
ProductID int,
Qty int
)
INSERT INTO #Test1 VALUES (1,104,2)
INSERT INTO #Test1 VALUES (1,199,1)
INSERT INTO #Test1 VALUES (2,100,3)
INSERT INTO #Test1 VALUES (3,828,1)
CREATE TABLE #Test2
(
ProductID int,
QtyInStock int
)
INSERT INTO #Test2 VALUES (104,3)
INSERT INTO #Test2 VALUES (199,1)
UPDATE #Test1 SET Qty=(SELECT QtyInStock-Qty
FROM #Test2 T WHERE T.ProductID=#Test1.ProductID)
WHERE EXISTS (SELECT * FROM #Test2
T WHERE T.ProductID=#Test1.ProductID)
DROP TABLE #Test1,#Test2
<craig.parsons@.crawfos.com> wrote in message
news:1136476091.334221.282250@.g44g2000cwa.googlegroups.com...
> Hi Folks,
> I have two tables, one of which I want to update from another.
> Essentiall I have a table of orders and a product table. I want to
> subtract the qty sold in the orders table from the QtyInStock column in
> the Products table, for every line in an order.
> But I dont really want to loop through each line in the order, either
> in application or with a cursor as something is telling me there must
> be a neater solution!
> Example Orders Table.
> OrderID ProductID Qty
> 1, 104, 2
> 1, 199, 1
> 2, 100, 3
> 3, 858, 1
> ProductID, QtyInStock
> 104, 3
> 199, 1
> etc ...
> As you can see I want to be able to run a query against OrderID 1,
> and it to reduce the QtyInStock column by the correct amount for
> products 104 and 199 as an example.
> Can this be done with one statement, or will I have to loop ?
>
> Thanks in Advance.
>
> Craig.
>|||Uri,
I think Craig wants to update the quantity in stock
from the Product table, not the quantity in the Orders
table, which your query updates. He could use Jens's
solution, or one like this:
UPDATE #Products SET
QtyInStock = QtyInStock - (
SELECT SUM(O.Qty)
FROM #Orders AS O
WHERE O.ProductID = #Products.ProductID
)
WHERE EXISTS (
SELECT * FROM #Orders
WHERE #Orders.ProductID = #Products.ProductID
)
Steve Kass
Drew University
Uri Dimant wrote:

>Hi
>CREATE TABLE #Test1
>(
> OrderID int,
> ProductID int,
> Qty int
> )
>INSERT INTO #Test1 VALUES (1,104,2)
>INSERT INTO #Test1 VALUES (1,199,1)
>INSERT INTO #Test1 VALUES (2,100,3)
>INSERT INTO #Test1 VALUES (3,828,1)
>
>CREATE TABLE #Test2
>(
> ProductID int,
> QtyInStock int
> )
>INSERT INTO #Test2 VALUES (104,3)
>INSERT INTO #Test2 VALUES (199,1)
>
>UPDATE #Test1 SET Qty=(SELECT QtyInStock-Qty
>FROM #Test2 T WHERE T.ProductID=#Test1.ProductID)
>WHERE EXISTS (SELECT * FROM #Test2
>T WHERE T.ProductID=#Test1.ProductID)
>
>
>DROP TABLE #Test1,#Test2
>
>
><craig.parsons@.crawfos.com> wrote in message
>news:1136476091.334221.282250@.g44g2000cwa.googlegroups.com...
>
>
>|||craig.parsons@.crawfos.com wrote:
> Hi Folks,
> I have two tables, one of which I want to update from another.
> Essentiall I have a table of orders and a product table. I want to
> subtract the qty sold in the orders table from the QtyInStock column
> in the Products table, for every line in an order.
> But I dont really want to loop through each line in the order,
> either in application or with a cursor as something is telling me
> there must be a neater solution!
> Example Orders Table.
> OrderID ProductID Qty
> 1, 104, 2
> 1, 199, 1
> 2, 100, 3
> 3, 858, 1
> ProductID, QtyInStock
> 104, 3
> 199, 1
> etc ...
> As you can see I want to be able to run a query against OrderID 1,
> and it to reduce the QtyInStock column by the correct amount for
> products 104 and 199 as an example.
> Can this be done with one statement, or will I have to loop ?
>
Here is the ANSI version (I used the Sum function to guarantee only a single
result would be returned):
UPDATE Products
SET QtyInStock = QtyInStock -
(SELECT Sum(Qty) FROM Orders o
WHERE o.OrderID = 1 AND o.ProductID = Products.ProductID)
The T-SQL version:
UPDATE p
SET QtyInStock = QtyInStock - o.Qty
FROM Products p inner join (
SELECT ProductID,Sum(Qty) AS Qty FROM Orders
WHERE OrderID = 1 GROUP BY ProductID) o
ON o.ProductID = p.ProductID
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.