Avoid Looping / Cursors. Help with Statement.

SQL Server Developer >> 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.

SQL Server240

SQL Server Developer >> Avoid Looping / Cursors. Help with Statement. What about doing this setbased:

UPDATE
Products
SET QtyInStock = QtyInStock - SoldItems.Qty
FROM Products
INNER JOIN
(
SELECT
SUM(Qty) AS Qty,ProductId
From Orders
GROUP BY ProductId
) SoldItems
ON Products.ProductId = SoldItems.ProductId

HTH, jens Suessmeyer.

SQL Server Developer >> Avoid Looping / Cursors. Help with Statement. 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

> 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.
>

SQL Server Developer >> Avoid Looping / Cursors. Help with Statement. 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

>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
>
>
>
>
>

>
>
>>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.
>>
>>
>>
>
>
>
>

SQL Server Developer >> 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 ?
>
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.

SQL Server Developer >> Avoid Looping / Cursors. Help with Statement. Guys,

Thank you to every one of you. I knew there was a better way, but my
understanding of SQL is basic / mediocre.

Can anyone of you recommend any good books? I am using SQL Server
2005.

Thanks.