February 19, 2020 by Kenneth Fisher
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.