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

 

 

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

 

 

上次翻译了一篇文章，揭示了UPDATE的同时操作特性（all-at-once），当时实现了列的同时互换。其实UPDATE还可以实现行的互换，本文来说说。

 

所谓列的互换当然不是指整条记录的互换，因为那样没有任何意义，而且那也仅仅是排序的工作。本文指的是记录的字段之间的互换，这是有实际意义的：很多表对记录的顺序有严格要求，表会根据关键字或专门字段进行排序，同时程序中也会有上移、下移的操作。

比如下表就是根据关键字ID来排序：

 

 

CREATETABLE TA([ID]INT PRIMARYKEY, [NAME] CHAR, [VAL] INT)

 

INSERTINTO 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为关键字，所以只有借助于临时值：

BEGINTRAN

UPDATE TA

SET ID = 10001

WHERE ID = 2

 

UPDATE TA

SET ID = 2

WHERE ID = 3

 

UPDATE TA

SET ID = 3

WHERE ID = 10001

COMMITTRAN

 

结果：

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

LEFTJOIN 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

LEFTJOIN 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