Set a variable and update a column at the same time.
11February 19, 2020 by Kenneth Fisher
The other day I saw a weird option for the UPDATE command in a stack overflow answer. Basically it looked like this:
CREATE TABLE #test (col1 INT, col2 INT, col3 INT); INSERT INTO #test VALUES (1,2,3), (2,2,3), (3,2,3), (4,2,3); DECLARE @var1 INT, @var2 INT, @var3 INT; UPDATE #test SET @var1 = col2 = 1;
So the update statement is updating all of the values of col2 to 1 and setting @var1 to 1 at the same time. And a few interesting notes. This only worked with one variable and one column and when the variable came first. i.e. Variable = Column = Value. The value can be a hard codeded value, variable, column name, calculated column etc. It doesn’t matter. But any variations won’t work.
UPDATE #test SET col2 = @var1 = 1; UPDATE #test SET @var1 = @var2 = 1; UPDATE #test SET col2 = col1 = 1;
And of course nothing with more than the 3 values (variable, column, value) will work.
I’m honestly not sure when this would be all that useful. Maybe when setting an update date/time? Regardless I love finding these weird little T-SQL tricks.
SQL Studies 
For a practical use case, I’ve used it like this to increment a new column added to a table to set a default order for items in the table.
DECLARE @id INT
SET @id = 0
UPDATE table
SET @id = Order = @id + 1
Hmm .. interesting. I’d probably use ROW_NUMBER though?
Aside from incrementing counters, it can also be used to concatenate strings. Or use other formulas.
Hi, Kenneth. This update technique (using variable) very useful and fast for solving common problem with filling null values, see more details in this great article: https://www.red-gate.com/simple-talk/sql/t-sql-programming/filling-in-missing-values-using-the-t-sql-window-frame/
Robyn Page did a deep dive a while back on replacing cursors with the Quirky Update. https://www.red-gate.com/simple-talk/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/
Actually it’s not a SQL trick but a (simplified) version of the situation in many programming languages where an expression itself (col = 1) returns a value which can be assigned to another variable (@var1). Probably wasn’t even intended to work; if done properly it would also have worked for e.g. @var1 = @var2 = 1. But that may also have to do with the overloading of the “=” operator for both “becomes” and “equals”. In other languages there are often separate tokens for these (in C “=” and “==”, in Algol68 “:=” and “=”) so there can be no confusion.
It used to be one of the only ways to do running totals and “data smears” without the slothfulness of RBAR. It’s still faster than a lot of the current methods but does require some fairly strict but easy to follow rules of use. Amongst the afficionados of the technique/method, it is known simply as the “Quirky Update”. It’s also lesser known name is the “3 part update”. It’s documented under UPDATE in Books Online (BOL) although the writers of BOL have a warning about it because they don’t actually have a clue how to use it correctly.
My now-very-old article on the subject can be found at the following URL. I still use the technique for certain things even on SQL Serve 2016 simply because of its performance.
https://www.sqlservercentral.com/articles/solving-the-running-total-and-ordinal-rank-problems-rewritten
That’s really cool Jeff. I’ve actually been reading some of the other responses that mention “data smears” etc and have been thinking I should do some speed tests.
interesting issues with this:
1 – A timestamp column will return the value as it existed before the update
2 – The value returned into the variable is from the last row, just like when you do this with a select with multiple rows
IF OBJECT_ID(‘tempdb..#test’) IS NOT NULL BEGIN
DROP TABLE #test
END
CREATE TABLE #test (col1 INT, col2 INT, col3 INT, last_modified DATETIME2(7) DEFAULT (SYSDATETIME()), ts TIMESTAMP);
INSERT INTO #test (col1, col2, col3) VALUES (1,2,3), (2,2,3), (3,2,3), (4,2,3);
DECLARE @var1 INT, @var2 INT, @var3 INT, @last_mod1 DATETIME2(7), @ts1 TIMESTAMP;
WAITFOR DELAY ’00:00:01′
SELECT * FROM #test
UPDATE #test SET @var1 = col1 = CASE WHEN col1 = 1 then 111 ELSE col1 END,
@var2 = col2 = 222,
@var3 = col3 = 333,
@last_mod1 = last_modified = SYSDATETIME(),
@ts1 = ts;
SELECT * FROM #test
SELECT @var1, @var2, @var3, @last_mod1, @ts1
it is important that the variable declaration matchs the data type of the table column, since there will be no implicit converting.
I made the mistake to declare a date variable to receive a datetime column which failed with an error message (saying that it was not the same data type).
Real example: I set some columns in the target table to the result of an aggregating subquery (e.g. total amount in the order table based on the aggregated prices in the order_positions) and needed this value later again in the procedure (okay, this example sounds like a bad cursor, in reality I do something similar with imported files with millions rows each, so a cursor over files will be okay here).
I read about it in an Itzik Ben-Gan’s book. He used it by creating a table that holds the last sequence number (something like Identity) and when you want to increase the sequence number you set the variable and update the table at the same time – if the transaction fails, your sequence number will rollback – that way you have a sequence without gaps.