How to write SQL UPDATE statement involving an aggregate and a JOIN

I have a table containing all of the line-items in all of my customer orders -let's call that table OrderDetail. It includes columns: ItemNum, Quantity, ItemStatus, ShipDate, among others.
I have another table, that I'll refer to as Inventory and it contains one entry per ItemNum. Besides ItemNum of course, it also has columns LastShipDate and TotalShipQuantity.
I attempted to write the query (see code below) that would update the Inventory table with LastShipDate and TotalShipQty for each ItemNum appearing in the OrderDetail table that have ItemStatus='S' (Shipped) and ItemNum that falls within a range (to keep the execution time reasonable), but I am getting the error "An aggregate may not appear in the set list of an UPDATE statement". I understand what the error is saying, but I don't know how to write the query to acomplish my goal without making SQL upset. It needs to be somewhat efficient also becasue my OrderDetail table has many millions of records. Thanks for your help!!
UPDATE Inventory
SET LastShipDate=max(OrderDetail.ShipDate), TotalShipQty=sum(OrderDetail.Quantity)
FROM         {oj OrderDetail RIGHT OUTER JOIN Inventory ON OrderDetail.ItemNum = Inventory.ItemNum }
WHERE OrderDetail.ItemNum BETWEEN (10000 and 11000) and OrderDetail.ItemStatus='S'
GROUP BY OrderDetail.ItemNum

Open in new window

bobPUNKbobAsked:
Who is Participating?
 
gnoonConnect With a Mentor Commented:
Try this
UPDATE Inventory
SET LastShipDate=OD.LastShipDate, TotalShipQty=OD.TotalShipQty
FROM Inventory LEFT OUTER JOIN (
    SELECT ItemNum, MAX(ShipDate) AS LastShipDate, MAX(Quantity) AS TotalShipQty
    FROM OrderDetail
    WHERE ItemNum BETWEEN (10000 and 11000) and ItemStatus='S'
    GROUP BY ItemNum) OD ON Inventory.ItemNum = OD.ItemNum

Open in new window

0
 
bobPUNKbobAuthor Commented:
Thanks!! Besides a SUM(Quantity) in place of MAX(Quantity), and for some reason the BETWEEN didn't work, so I had to use <>, it worked great. Thank you for your help!
Here is the final query that worked:

UPDATE Inventory
SET LastShipDate=OD.LastShipDate, TotalShipQty=OD.TotalShipQty
FROM Inventory LEFT OUTER JOIN (
    SELECT ItemNum, MAX(ShipDate) AS LastShipDate, SUM(Quantity) AS TotalShipQty
    FROM OrderDetail
    WHERE ItemNum >= 10000 and ItemNum < 11000 and ItemStatus='S'
    GROUP BY ItemNum) OD ON Inventory.ItemNum = OD.ItemNum
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.