Query Question: Error with subquery

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)

Query Question: Error with subquery

Author

Topic

ccomstock2
Starting Member

18 Posts

Posted - 2003-08-12 : 11:45:09

I am writing a sproc to combine orders being shipped to the same location. There is tblOrders with the order info and tblOrders_Products with products for each order. The sproc is given the two identical orders.

Here is the statement that works for items that are not common to both orders.

UPDATE tblOrders_Products
SET orderID = @orderID1
WHERE orderID = @orderID2 and itemID not in (Select itemID from tblOrders_Products where orderID = @orderID1)

For duplicate items, I have created a temporary table for those items.

INSERT INTO #duplicateProducts (orderID, itemID, opQty) select orderID, itemID, opQty from tblOrders_Products where orderID=@orderID2 AND itemID in (select itemID from tblOrders_Products where orderID=@orderID1)

Then I do an update.

UPDATE tblOrders_Products
SET opQty=opQty + (select opQty from #duplicateProducts where itemID IN (SELECT itemID from #duplicateProducts))
WHERE orderID=@orderID1 and itemID IN (SELECT itemID from #duplicateProducts)

The error occurs when the second order has two items that are the same as the first order. It doesn't like the subquery in the expression to add the qtys. Any suggestions of how to avoid this ??

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-12 : 13:09:55

Dont use a subquery for the update, use a JOIN:

UPDATE Orders
SET Orders.opQty = Orders.opQty + Duplicates.OpQty
FROM
tblOrders_Products Orders
INNER JOIN
#duplicateProducts Duplicates
ON
Orders.ItemID = Duplicates.ItemID

If you look at your original subquery, there are a lot of redundencies in the statement, and there is no correlation between tblOrders_Products and #duplicateProducts.

Owais

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources