Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 978
  • Last Modified:

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

0
bobPUNKbob
Asked:
bobPUNKbob
1 Solution
 
gnoonCommented:
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now