UPDATE 的同时操作特性 行的同时互换
上次翻译了一篇文章 ,揭示了 UPDATE 的同时操作特性( all-at-once ),当时实现了列的同时互换。其实 UPDATE 还可以实现行的互换,本文来说说。
所谓列的互换当然不是指整条记录的互换,因为那样没有任何意义,而且那也仅仅是排序的工作。本文指的是记录的字段之间的互换,这是有实际意义的:很多表对记录的顺序有严格要求,表会根据关键字或专门字段进行排序,同时程序中也会有上移、下移的操作。
比如下表就是根据关键字 ID 来排序:
CREATE TABLE TA( [ID] INT PRIMARY KEY , [NAME] CHAR , [VAL] INT )
INSERT INTO TA SELECT 1, 'A' , 121 UNION ALL SELECT 2, 'B' , 212 UNION ALL SELECT 3, 'C' , 454 UNION ALL SELECT 4, 'D' , 332
结果: ID NAME VAL ----------- ---- ----------- 1 A 121 2 B 212 3 C 454 4 D 332 |
现在要求将 2 , 3 两行的 ID 互换。若是按照以前的方法,因为 ID 为关键字,所以只有借助于临时值:
BEGIN TRAN UPDATE TA SET ID = 10001 WHERE ID = 2
UPDATE TA SET ID = 2 WHERE ID = 3
UPDATE TA SET ID = 3 WHERE ID = 10001 COMMIT TRAN
结果: ID NAME VAL ----------- ---- ----------- 1 A 121 2 C 454 3 B 212 4 D 332 |
此法比较复杂,而且必须将 3 个 UPDATE 操作放入同一个事务中。若我们借助于 UPDATE 的同时操作特性,可以使用下面的方法实现:
UPDATE A SET A. ID = B. ID FROM TA A JOIN TA B ON A. ID IN ( 2, 3) AND B. ID IN ( 2, 3) AND A. ID <> B. ID
结果: ID NAME VAL ----------- ---- ----------- 1 A 121 2 C 454 3 B 212 4 D 332 |
分析这句语句是如何实现的(生成的查询):
SELECT * FROM TA A JOIN TA B ON A. ID IN ( 2, 3) AND B. ID IN ( 2, 3) AND A. ID <> B. ID
结果: ID NAME VAL ID NAME VAL ----------- ---- ----------- ----------- ---- ----------- 3 C 454 2 B 212 2 B 212 3 C 454
|
两条记录肯定是同时更新的,因为稍有前后就会因为关键字重复而失败。这就是 UPDATE 的同时操作特性,下面举例将整个表所有的 ID 轮番更新(生成的查询):
SELECT * FROM TA A LEFT JOIN TA B ON A. ID % 4 = ( B. ID + 1) % 4
ID NAME VAL ID NAME VAL ----------- ---- ----------- ----------- ---- ----------- 1 A 121 4 D 332 2 B 212 1 A 121 3 C 454 2 B 212 4 D 332 3 C 454 |
使用 UPDATE 的同时操作特性,同时更新所有的行,结果如下:
UPDATE A SET A. ID = B. ID FROM TA A LEFT JOIN TA B ON A. ID % 4 = ( B. ID + 1) % 4
ID NAME VAL ----------- ---- ----------- 1 B 212 2 C 454 3 D 332 4 A 121
|