SQL Server - why are window functions not permitted in update statements?

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.

Window functions aren't permitted in UPDATE statements because UPDATE isn't compatible with SELECT or ORDER BY.

Window functions are like scoped SELECT statements that re-examine the relevant rows and apply conditions like PARTITION BY and ORDER BY. In addition, many window functions require an ORDER BY clause (ROW_NUMBER, LAG, and FIRST_VALUE, for example).

UPDATE statements use SET instead of SELECT, so SELECT is not allowed anywhere in the same query level. Any SELECT appearing with UPDATE must be contained in a subquery.

Disallowing the ORDER BY makes sense considering an UPDATE statement is indifferent to the order in which it updates rows.

There's no inherent downside to using a CTE or other subquery as a workaround to get an UPDATE to use a window function. That's the common practice advocated by T-SQL experts like Itzik Ben-Gan. (See page 29 of his book, Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions where he covers this exact scenario.)

StackExchange Database Administrators Q#145612, answer score: 9