UPDATE的同时操作特性 行的同时互换

 

 

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