Set a variable and update a column at the same time.

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.