I need to use a Windowed function in an Update statement but keeps on getting Null values.
As per below I need to calculate a Weighting value. If I use this in a Select statement it works fine, but I need to do it in an Update statement. What is the best alternative if this is not working?
;with CTE as ( select r.BuyNominal / SUM(r.BuyNominal) OVER(PARTITION BY r.PortfolioID, r.BuyInstr_id, r.buybrokercode, r.BuyCurrency ) AS new_Weighting from #ResultsFX as r) UPDATE #ResultsFX set Weighting = new_Weighting from CTE C join #ResultsFX r on r.Weighting = C.new_Weighting