June 9, 2015 at 8:55 am
SET @id = id= @id + 1
June 9, 2015 at 10:08 am
It's a statement type often employed in what is called the "quirky update" method of calculating a running total.
It basically leverages an odd behavior of SET when used in UPDATEs, which allows you to assign a value to the column being updated (using an expression with a variable), and then redirect that assignment to the variable, so that it is appropriately updated for each row.
The behavior is odd, but it performs very well, so you'll often see it included in comparisons of methods to calculate running totals (usually with a stern warning not to have production processes rely on this).
One good article on this is http://sqlperformance.com/2012/07/t-sql-queries/running-totals.
In this case, it seems it's just being used to set an ID column to a value incremented by 1 each row. For each row, it takes the value of the @id variable, adds 1 to it, and then sets the value of the ID column for the current row to the result. It then assigns that value to the variable as well, so that for each row the ID is equal to the previous row's ID+1.
Hopefully that clears it up a bit.
Cheers!
June 10, 2015 at 7:11 am
Jacob Wilkins (6/9/2015)
It's a statement type often employed in what is called the "quirky update" method of calculating a running total.It basically leverages an odd behavior of SET when used in UPDATEs, which allows you to assign a value to the column being updated (using an expression with a variable), and then redirect that assignment to the variable, so that it is appropriately updated for each row.
The behavior is odd, but it performs very well, so you'll often see it included in comparisons of methods to calculate running totals (usually with a stern warning not to have production processes rely on this).
One good article on this is http://sqlperformance.com/2012/07/t-sql-queries/running-totals.
In this case, it seems it's just being used to set an ID column to a value incremented by 1 each row. For each row, it takes the value of the @id variable, adds 1 to it, and then sets the value of the ID column for the current row to the result. It then assigns that value to the variable as well, so that for each row the ID is equal to the previous row's ID+1.
Hopefully that clears it up a bit.
Cheers!
Making mention of "quirky update" without revealing it's "gotcha" is "incomplete", so here goes:
Using that kind of SET statement to perform a "quirky update" comes at a price. For it to work, the table being updated MUST have a primary key. I can't recall if it has to be a clustered index or not, but I suspect that's the case. Check out the article the previous poster supplied a link to. Additionally, even if you specify an ORDER BY clause, the update WILL go in order by the primary key, and even then, it's not guaranteed to work correctly. Now you know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply