T-SQL 与..更新示例说明

Question

我只是找不到文档或解释以下查询的某个部分是如何工作的。

查询：

;WITH ToUpdate AS (
SELECT Id, 
Foo, 
FIRST_VALUE(Foo) OVER (ORDER BY Id) + 
ROW_NUMBER() OVER (ORDER BY Id) - 1 AS newFoo
FROM mytable
WHERE Bar= 1 
AND Id >= 111
)
UPDATE ToUPDATE
SET Foo = newFoo

它来自SO用户Giorgos Betsos，作为另一个类似于下面描述的问题的问题的答案。

假设我们有一个这样的表：

Id   Foo  Bar
2     5    1 
3     6    1
13   111   22
111     7    1
122    16    1
154    17    1
176    18    1

我们希望使用一个查询，该查询将：

• 使用Bar=1和Id >= 111取所有行
• 按 Id 升序对行进行排序
• 更新所有Foo，使新值等于前一行的值，从带有Id = 111的行开始递增 1

因此，生成的表将如下所示：

Id   Foo  Bar
2     5    1 
3     6    1
13   111   22  <-- not affected 
111     7    1
122     8    1
154     9    1
176    10    1

---

现在，查询效果很好，甚至在这里给出了一个例子：示例

但是我不明白为什么WITH/UPDATE组合有效？

为什么更新基础表？乍一看，我认为只有 WITH 返回的视图/临时表才会更新，而基础表保持不变。

Answer 1

更新 CTE 中的列时，SQL Server 会将该列追溯到其源表。 在这种情况下，mytable表有一个名为Foo的列，因此它实际上会在mytable表中更新。

顺便说一下，CTE 不是任何类型的临时表，甚至也不是视图。 它只是包装SQL代码/功能的一种便捷方式。 在引擎盖下，我希望 CTE 实际上会编译成一个典型的UPDATE语句，你可以运行EXPLAIN来说服自己这一点。

您可能想知道，如果您尝试对涉及无法追溯到物理表的列的 CTE 进行UPDATE，会发生什么。 在这种情况下，您将收到如下所示的错误：

更新或插入视图或函数"cte"失败，因为它包含派生字段或常量字段。

错误消息说明了一切。 您尝试更新物理表中实际上不存在的列，SQL Server 通过拒绝执行更新来抗议。