Avoid Looping / Cursors. Help with Statement.

5 Jan 2006 3:48 PM

craig.parsons

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.

5 Jan 2006 4:03 PM

Uri Dimant

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.pars***@crawfos.com> wrote in message
Show quote

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

5 Jan 2006 4:14 PM

Steve Kass

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:

Show quote

>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.pars***@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.
>>
>>
>>
>
>
>
>

5 Jan 2006 4:03 PM

Jens

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.

5 Jan 2006 4:13 PM

Bob Barrows [MVP]

craig.pars***@crawfos.com wrote:
Show quote

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

6 Jan 2006 3:49 PM

craig.parsons

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.