sql--Magic Code 1 update

Description: A loop with the update code. Very simple, but in the QQ group asked, it should be said that few people pay attention to this problem, that is, few people really understand the SQL update.

The code is as follows:

if object_id('tempdb: #TB') is  not NULL    Drop Table#TBGo--A temporary table was createdCreate Table#TB (IDint)--inserting 5 RecordsInsert  into#TB (ID)Select 1Union  AllSelect 2Union  AllSelect 3Union  AllSelect 4Union  AllSelect 5--View RecordsSelect *  from#TBGo--Code 1Declare @i int Set @i = 1 while @i < 5begin    Update#TBSetId=Id@i = @i + 1    Select @iEndGo--Code 2Declare @i intSet @i = 1 while @i < 5begin    Update#TBSetId=IDSet @i = @i + 1    Select @i    End

From the code to see nothing but to create a temporary table, insert 5 records, and then code 1 and Code 2 is not too big difference, the first time to see that 2 times the cycle is 4 times, the results of @i should be 5, if you think so, advise you to execute the code again, you think, you think is wrong.

The correct result is:

Code 1 Cycle times 1 times, @i results 6

Code 2 Cycle times 4 times, @i results 5

See here, in good back to understand the code, especially to understand the next update, and then look at the explanation behind.

Code Explanation:

the difference between the two ends of the code is only in the update line, code 1 to write 2 small statements on one line, and code 2 in two lines to write, this is the problem. Code 1 performs the analysis:@i=1 while（@i <5{Execute update by looping, because update is a record of the Change ID field and the number of records is 5, then the code for the update line executes 5 times, so@i = @i +1 also carried out 5 times, after the completion of the@i=6Output@iCyclic Judgment (@i <5false out} says that the number of cycles for code 1 is 1, but@i=6Code 2 performs the analysis:@i=1 while（@i <5{Execute update by looping, because update is a record of the Change ID field and the number of records is 5, then the code for the update line executes 5 times, but code 2 does not@i = @i +1 So after the completion,@i=1@i = @i + 1，@i = 2Output@iCyclic Judgment (@i <5true to execute the loop again, knowing false} says that the number of cycles for code 2 is 4, but@i=5

View Code

Reference: In this case, I can only guess the mechanism of update itself. For code 1, each effect record is executed once @i = @i + 1. So in a loop, it executes 5 times, reaching the while threshold, jumping out of the loop.

Reference: http://www.cnblogs.com/Gin-23333/p/4127453.html

sql--Magic Code 1 update