update with aggregate function 1

[mlabac]

I need to write an update statement that updates POExtendedTotal grouped by the PONumber with the sum of ItemExtendedCost

I believe I am on the write track as my select statement is returning the right results, but the update statement does not recognize the alias SumOfItemExtendedCost

UPDATE FulfilmentOrders
SET FulfilmentOrders.POExtendedTotal = SumOfItemExtendedCost
From FulfilMentOrders
Where FulfilmentOrders.POnumber IN (SELECT FulfilmentOrders.PONumber, Sum(FulfilmentOrders.ItemExtendedCost) AS sumOfItemExtendedCost
FROM FulfilmentOrders
GROUP BY FulfilmentOrders.PONumber, FulfilmentOrders.Disti_ID, FulfilmentOrders.OrderSent
HAVING FulfilmentOrders.Disti_ID=13 AND FulfilmentOrders.OrderSent=0)


Thoughts?

 

[JohnDTampaBay]

How does this work for you? Good luck!

UPDATE f
SET f.POExtendedTotal = d.SumOfItemExtendedCost
FROM FulfilMentOrders AS f
    INNER JOIN (
        SELECT PONumber,
            Sum(ItemExtendedCost) AS sumOfItemExtendedCost
        FROM FulfilmentOrders
        WHERE Disti_ID=13
        AND OrderSent=0
        GROUP BY PONumber
     ) AS d
     ON f.POnumber = d.PONumber

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[mlabac]

That worked!!! thanks for your help!