CODEWITHSUNDEEP
sql-servert-sql
uncool
uncool

Reputation: 2703

Computed value with LAG() in UPDATE / SET statement throws error

I have the following SQL statement that is causing me some trouble (I'm very new to tsql).

   UPDATE #temp
   SET roe = net_income / LAG(equity, 1) OVER (ORDER BY [year])

   SELECT net_income / ( LAG(equity, 1) OVER (ORDER BY [year]) ) as roe FROM #temp -- this select statement works just fine however.

   SELECT * FROM #temp -- please see picture below for data

enter image description here

I receive the following error: Windowed functions can only appear in the SELECT or ORDER BY clauses.

What is causing this? Running the code in a select statement works just fine.

Upvotes: 0

Views: 31

Answers (1)

Thom A
Thom A

Reputation: 95826

As mentioned, you need to use an updatable CTE here:

WITH CTE AS(
    SELECT roe,
           net_income / LAG(equity, 1) OVER (ORDER BY [year]) AS NewRoe
    FROM #Temp)
UPDATE CTE
SET roe = NewRoe;

If equity could have a value of 0, I would also suggest wrapping the LAG expression in a NULLIF.

Upvotes: 1

Related Questions