(轉)SQL SERVER中 UPDATE的赋值次序

水深无声 2022-08-25 13:54 197阅读 0赞

本文轉自:http://blog.csdn.net/zxkid/archive/2007/04/24/1579870.aspx

**1) 先变量再字段**

结果:  
Id1 Id2  
\----------- -----------  
2 1  
3 2  
4 3

Id1 Id2  
\----------- -----------  
2 11  
3 12  
4 13

**2) 变量之间, 从左到右**

![None.gif][] SET  NOCOUNT  ON ;  
![None.gif][]  
![None.gif][] DECLARE  @i  **INT** ,  @j  **INT**   
![None.gif][] DECLARE  @Table  TABLE   
![None.gif][](  
![None.gif][] Id1  **INT** ,  
![None.gif][] Id2  **INT**   
![None.gif][]);  
![None.gif][]  
![None.gif][] INSERT  @Table (Id1, Id2)  
![None.gif][] SELECT  **1** ,  **10** ;  
![None.gif][]  
![None.gif][] SELECT  @i  =  **1** ,  @j  =  **0**   
![None.gif][] UPDATE  @Table  SET  @j  =  @i ,  @i  =  @i  \+  **1**   
![None.gif][] PRINT  ' @i =  '  \+  CAST ( @i  AS  **VARCHAR** )  \+  ' , @j =  '  \+  CAST ( @j  AS  **VARCHAR** )  
![None.gif][]  
![None.gif][] SELECT  @i  =  **1** ,  @j  =  **0**   
![None.gif][] UPDATE  @Table  SET  @i  =  @i  \+  **1** ,  @j  =  @i   
![None.gif][] PRINT  ' @i =  '  \+  CAST ( @i  AS  **VARCHAR** )  \+  ' , @j =  '  \+  CAST ( @j  AS  **VARCHAR** )  
![None.gif][]  
![None.gif][] SELECT  @i  =  **1** ,  @j  =  **0**   
![None.gif][] UPDATE  @Table  SET  @i  =  @j  \+  **1** ,  @j  =  @i   
![None.gif][] PRINT  ' @i =  '  \+  CAST ( @i  AS  **VARCHAR** )  \+  ' , @j =  '  \+  CAST ( @j  AS  **VARCHAR** )  
![None.gif][]  
![None.gif][] SELECT  @i  =  **1** ,  @j  =  **0**   
![None.gif][] UPDATE  @Table  SET  @i  =  @j ,  @j  =  @i   
![None.gif][] PRINT  ' @i =  '  \+  CAST ( @i  AS  **VARCHAR** )  \+  ' , @j =  '  \+  CAST ( @j  AS  **VARCHAR** )  
![None.gif][]  
![None.gif][] SET  NOCOUNT  OFF ;

结果:  
@i = 2, @j = 1  
@i = 2, @j = 2  
@i = 1, @j = 1  
@i = 0, @j = 0

**3) 字段之间, 并行执行**

![None.gif][] SET  NOCOUNT  ON ;  
![None.gif][]  
![None.gif][] DECLARE  @Table  TABLE   
![None.gif][](  
![None.gif][] Id1  **INT** , Id2  **INT**   
![None.gif][]);  
![None.gif][]  
![None.gif][] INSERT  @Table (Id1, Id2)  
![None.gif][] SELECT  **1** ,  **10**   
![None.gif][] UNION  ALL   
![None.gif][] SELECT  **2** ,  **20**   
![None.gif][] UNION  ALL   
![None.gif][] SELECT  **3** ,  **30** ;  
![None.gif][]  
![None.gif][] UPDATE  @Table  SET  Id1  =  Id2, Id2  =  Id1  
![None.gif][] SELECT  \*  FROM  @Table   
![None.gif][]  
![None.gif][] SET  NOCOUNT  OFF ;

结果:  
Id1 Id2  
\----------- -----------  
10 1  
20 2  
30 3

[None.gif]: /images/20220824/70f5771458c34d35a66094b0c9c131ee.png