Complicated Update Statement - 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 2005 Forums

Transact-SQL (2005)

Complicated Update Statement

Author

Topic

r1pt1de
Starting Member

2 Posts

Posted - 2007-01-19 : 16:17:29

**THIS SELECT STATEMENT WORKS PROPERLY AND RETURNS 14 ROWS***

SELECT PurchaseOrders.PurchaseOrderNumber
FROM PurchaseOrders INNER JOIN POLineItems ON PurchaseOrders.PurchaseOrderNumber = POLineItems.PurchaseOrdernumber
GROUP BY PurchaseOrders.PurchaseOrderNumber
HAVING SUM(POLineItems.QuantityOutstanding) = '0'

**THIS UPDATE COMMAND DOES NOT WORK THEY WAY IM INTENDING IT TO***

UPDATE PurchaseOrders
SET Completed = '1'
WHERE EXISTS (SELECT PurchaseOrders.PurchaseOrderNumber
FROM PurchaseOrders INNER JOIN
POLineItems ON PurchaseOrders.PurchaseOrderNumber
= POLineItems.PurchaseOrdernumber
GROUP BY PurchaseOrders.PurchaseOrderNumber
HAVING SUM(POLineItems.QuantityOutstanding) = '0'
)

Quick break down of the tables for understanding purposes

PurchaseOrders has several rows including PurchaseOrderNumber (Primary Key), and Completed.

POLineItems contains the rows PurchaseOrderNumber (not unique in this table), and QuantityOutstanding.

I need completed to be set to 1 for a PurchaseOrderNumber when the SUM of QuantityOutstanding = 0 for said PurchaseOrderNumber

There are 14 instances of this, and I can get it to select them properly using the above SELECT statement, but I can not figure out how to make it update only the relevant rows in PurchaseOrders table.

The above UPDATE statement updates all 51 rows in the PurchaseOrders table instead of just the 14 that match the criteria.

any help on this would be greatly appreciated

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-01-22 : 06:20:15

You need to associate the subquery with the main query, and you then don't need PurchaseOrders in your subquery. I.e.

WHERE EXISTS (SELECT pol.PurchaseOrderNumber
POLineItems AS pol
WHERE pol.PurchaseOrderNumber = PurchaseOrders.PurchaseOrderNumber
GROUP BY pol.PurchaseOrderNumber
HAVING SUM(pol.QuantityOutstanding) = 0
)

I'd be inclined to convert the subquery to a derived table and inner join:

UPDATE po
SET po.Completed = '1'
FROM PurchaseOrders AS po
JOIN (SELECT pol.PurchaseOrderNumber
FROM POLineItems AS pol GROUP BY pol.PurchaseOrderNumber
HAVING SUM(pol.QuantityOutstanding) = 0
) AS pol
ON po.PurchaseOrderNumber = pol.PurchaseOrderNumber

Mark

r1pt1de
Starting Member

2 Posts

Posted - 2007-01-22 : 08:38:53

Mark you are the man, thank you very very much. I sat there for several hours trying to figure out what was wrong with my query

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-01-22 : 09:33:41

No worries!

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources