Windowed functions in Update statement

1

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
2

one way is to use the windowed function into a new table !!!

and then use the new table in Update !!

3

So the where clause says if they the
same update the row with the same value?

4

Apologies, my mistake
I also tried this without success:

UPDATE
#ResultsFX
SET
Weighting = sub.new_Weighting
FROM
(
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
) sub
WHERE
Weighting = sub.new_Weighting

5

Maybe:

WITH NewWeighting
AS
(
	SELECT * -- column list
		,SUM(BuyNominal) OVER
			(PARTITION BY PortfolioID, BuyInstr_id, buybrokercode, BuyCurrency) as new_Weighting
	FROM #ResultsFX
)
UPDATE NewWeighting
SET Weighting = BuyNominal\new_Weighting
WHERE new_Weighting IS NOT NULL
   AND BuyNominal IS NOT NULL;
1 Like
6

Try this:

   With NewWeighting
     As (
 Select *       -- include key column(s)
      , rfx.BuyNominal
      , rfx.Weighting
      , new_Weighting = sum(rfx.BuyNominal) over(Partition By rfx.PortfolioID
                                                            , rfx.BuyInstr_id
                                                            , rfx.buybrokercode
                                                            , rfx.BuyCurrency)
   From #ResultsFX              rfx
        )
 Update NewWeighting
    Set Weighting = BuyNominal \ new_Weighting
  Where new_Weighting <> 0              -- avoid divide by zero error
    And Weighting <> new_Weighting;     -- if it hasn't changed, don't need to update it

If you currently have NULL values in the Weighting column - then you should update the step that creates the temp table to change that to a 0 instead. If that isn't possible - then modify this line:

      , Weighting = coalesce(rfx.Weighting, 0)
2 Likes
7

hi

please see the below links !! if thy help :slight_smile:

https://www.sqlshack.com/inserts-and-updates-with-ctes-in-sql-server-common-table-expressions/

1 Like