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

11

February 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.

11 thoughts on “Set a variable and update a column at the same time.

  1. Jason says:

    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

  2. Konstantin Taranov says:

    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/

  3. Giles says:

    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/

  4. Ben says:

    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.

  5. Jeff Moden says:

    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

  6. Tim Cartwright says:

    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

  7. 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).

  8. Tom says:

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,106 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: