CODEWITHSUNDEEP
sql-server
tchock
tchock

Reputation: 245

Update/Set ordering?

I'm curious as to why the following update statement is not setting as expected:

DECLARE @int INT
SET @int = 0;

UPDATE #jc_TEMP
SET Num =  @int, @int = @int + 1

I would expect this to set the first row to 0 and THEN update. Do local variables get set first, before fields?

Upvotes: 2

Views: 70

Answers (1)

Xedni
Xedni

Reputation: 4695

The process of doing an update on a table with a variable that then gets assigned repeatedly in the same statement is sometimes referred to as a "quirky update". It's an undocumented feature of SQL Server which, if controlled correctly will quickly update rows in the order of the primary key. I've used it on a few occasions for things like running totals in pre-2012. There are quite a few gotchas, as with any undocumented procedure, but a good intro is this article from SSC

http://www.sqlservercentral.com/articles/T-SQL/68467/

To answer the first question, yes. variables get evaluated first. I just know this from trial and error, so I can't point you to a specific article documenting that behavior.

Be warned as I mentioned above that unless you do this right, you can't be guaranteed of the order in which the updates will occur. If you're doing this in a production system, I'd recommend joining the table to itself and using the row_number() window function instead. Something like:

update a
set num = x.num
from #jc_temp a
inner join (select num = row_number() over (order by num)
            from #jc_temp) x
    on a.num = x.num

Upvotes: 1

Related Questions