SQLTeam.com | Weblogs | Forums

Windowed functions in Update statement

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

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

and then use the new table in Update !!

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

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

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

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)
1 Like

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