We help IT Professionals succeed at work.

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

Medium Priority
990 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.