Link to home
Start Free TrialLog in
Avatar of bobPUNKbob
bobPUNKbob

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of gnoon
gnoon
Flag of Thailand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bobPUNKbob
bobPUNKbob

ASKER

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