在同一张表中使用另一列的值更新某一列

Question

请考虑以下SQL语句。

Create table abc 
(A int,
B int
)

insert into abc values (1,2)

下面两个语句的作用是相同的，为什么？

update abc 
set A = B,
B =0
where A=1

并且

update abc 
set B =0,
A = B
where A=1

我认为在后面的语句中，首先设置了B列的值，然后将A列的值设置为B列的值。

- IsmailS

Answer 1

单个更新语句是原子性的，其各个部分没有顺序。

以下两种情况：

update abc set A = B, B = 0 where A=1
update abc set B = 0, A = B where A=1

因为两个赋值被认为是同时发生的，所以它们会完全执行相同的操作。

换句话说，等号右边的B是B的旧值。

---

补充说明：DBMS如何实现此行为取决于编写DBMS的人的智慧。

例如，一个DBMS 可能会尝试锁定所有A值为1的行，然后一次执行每一行上的A = B，B = 0（按照执行引擎的规定顺序来设置A为B之前先改变B）。

像set A = B, B = A这样的语句需要更多的智能，但它可以通过先保存当前行并使用那里的值来设置新行中的值来轻松完成，类似于：

read in oldrow
copy oldrow to newrow
newrow.A = oldrow.B
newrow.B = oldrow.A
write out newrow

然后它将解锁所有行。

这只是其中一个选项。一个非常愚蠢的DBMS可能会锁定整个数据库文件，尽管这不会为并发性带来很聪明的解决方案。

单用户、单线程的DBMS根本不需要考虑并发性。它将不锁定任何内容，只需遍历每个相关的行，并进行更改。

- paxdiablo

---

读取和写入是否同时发生？这不会在 SQL Server 工作方式中创建任何并发吗？ - IsmailS

Answer 2

SQL不按字段顺序评估值。就SQL而言，这些语句是相同的。

更新操作是一步完成（原子性），而不是多个有序操作。

SQL访问表中的每一行，然后将A更新为B的当前值，并同时将B更新为0。

如果这有助于您理解，您可以将其视为更新触发器中发生的情况，该触发器可以访问DELETED逻辑表中的表的当前值，并访问INSERTED逻辑表中的新值。

- Oded

---

读取和写入是否同时发生？这不会在 SQL Server 工作方式中创建任何并发吗？ - IsmailS

如果你观察这个语句，实际上我正在读取变量 B 的值并将其设置为变量 A。 - IsmailS

@Ismail - 你正在进行一个单一的更新。SQL服务器保证这个单一的更新是原子性的。 - Oded

同意。但是你不觉得 B 的值被读取并设置为 A 了吗？ - IsmailS

@Ismail - 这是SQL内部的_实现_细节。重要的是操作是原子性的。SQL如何实现这一点是另一回事。 - Oded

Answer 3

@Ismail的答案有所改进：从逻辑上讲，更新操作是一次删除和插入操作的组合，因此分别在逻辑表“deleted”和“inserted”中得到一行。这里的工作单元是行：当行被更新时，它将使用新值进行删除并重新插入（如果您想知道哪些列实际上发生了更改，您必须自己计算）。

一个有用的提示是：

UPDATE MyTable
   SET A = B, B = A;

将转置列的值。

- onedaywhen

Answer 4

现在我明白了，当我们运行更新语句时，会涉及到两个逻辑表，即DELETED和INSERTED。

因此，设置为A列的值是从DELETED表的B列检索出来的，而被设置为0的B的值则来自于INSERTED表。

- IsmailS