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!!
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is the final query that worked:
UPDATE Inventory
SET LastShipDate=OD.LastShipDa
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