Ao executar uma instrução de atualização, como a abaixo, recebo um erro informando que
As funções em janela só podem aparecer nas cláusulas SELECT ou ORDER BY.
UPDATE dbo.Dim_Chart_of_Account
SET Account_Order = LAG([Account_Order]) OVER (ORDER BY [Account_SKey])
Eu sei que isso pode ser facilmente contornado usando um cte atualizável, como abaixo
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
Minha pergunta é: há algum motivo pelo qual isso não é permitido em uma instrução de atualização? Devo evitar o uso de um cte atualizável como solução alternativa?
Minha preocupação é que há problemas ao usar funções de janela com instruções de atualização e, portanto, gostaria de entender se esse é um método aceitável ou deve ser evitado.
Funções de janela não são permitidas em instruções UPDATE porque UPDATE não é compatível com SELECT ou ORDER BY.
As funções de janela são como instruções SELECT com escopo que reexaminam as linhas relevantes e aplicam condições como PARTITION BY e ORDER BY. Além disso, muitas funções de janela requerem uma cláusula ORDER BY (ROW_NUMBER, LAG e FIRST_VALUE, por exemplo).
As instruções UPDATE usam SET em vez de SELECT, portanto, SELECT não é permitido em nenhum lugar no mesmo nível de consulta. Qualquer SELECT que apareça com UPDATE deve estar contido em uma subconsulta.
Não permitir o ORDER BY faz sentido, considerando que uma instrução UPDATE é indiferente à ordem em que atualiza as linhas.
Não há desvantagem inerente em usar um CTE ou outra subconsulta como uma solução alternativa para obter um UPDATE para usar uma função de janela. Essa é a prática comum defendida por especialistas em T-SQL como Itzik Ben-Gan. (Consulte a página 29 de seu livro, Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions , onde ele aborda esse cenário exato.)