When running an update statement, such as the one below, I get an error telling me that
Windowed functions can only appear in the SELECT or ORDER BY clauses.
UPDATE dbo.Dim_Chart_of_Account SET Account_Order = LAG([Account_Order]) OVER (ORDER BY [Account_SKey])
I know that this can be easily worked around using an updatable cte, like below
WITH my_cte AS ( SELECT [Account_Order], LAG([Account_Order]) OVER (ORDER BY [Account_SKey]) AS acc_order_lag FROM Dim_Chart_of_Account ) UPDATE my_cte SET [Account_Order] = acc_order_lag
My question is, are there any reasons why this is not permitted in an update statement, should I avoid using an updatable cte as a workaround?
My concern is that there are issues when using window functions with update statements and therefore I'd like to understand if this is an acceptable method or should be avoided.