sql - SQL：如何使用窗口函数更新来自特定选择的列值（东西，密集排名，结束）

Question

这就是我想做的：

UPDATE TableA
SET ColumnA = SELECT stuff(ColumnA, 1, 2, 11+dense_rank() over(order by left(ColumnA, 10))) from TableA
WHERE Year = '2021'
AND Type = 'LA'

我想将列的值设置为该 SELECT 的值

标签： sqlsql-server

Answer 1

您可以使用可更新的 CTE：

WITH cte AS (
    SELECT *, STUFF(ColumnA, 1, 2, 11 +
                    DENSE_RANK() OVER (ORDER BY LEFT(ColumnA, 10))) AS new_val
    FROM TableA
    WHERE Year = '2021' AND Type = 'LA'
)

UPDATE cte
SET ColumnA = new_val;