CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Schmalls
Schmalls

Reputation: 1494

Updating Column Based On Another Updated Column

My question has to do with the order of updates in a single update statement. I have observed that when I set variables using a SELECT statement, that the variables are set in order. For example:

SELECT
    @Test1 = 2,
    @Test2 = @Test1 + 1

At this point @Test1 will be 2 and @Test2 will be 3 because the set operations are done in order. But what happens with UPDATE?

UPDATE TestTable SET
    Test1 = 2,
    Test2 = Test1 + 1

Will Test2 use the initial value of Test1 in its calculation or will it use the value we just set? Does it make any difference if it is an UPDATE statement inside of a MERGE?

MERGE INTO TestTable AS T
    USING (
        SELECT
                Test1,
                Test2
            FROM SomeOtherTable
        ) AS S
        ON S.Test1 = T.Test1
    WHEN MATCHED THEN
        UPDATE SET
            T.Test1 = 2,
            T.Test2 = T.Test1 + 1
    ;

Upvotes: 3

Views: 1436

Answers (1)

Mark Byers
Mark Byers

Reputation: 839054

The names on the right hand side of the assignment refer to the old values of the columns, regardless of the order they are in.

This (for example) allows you to swap two values without using a temporary variable.

UPDATE foo
SET a = b,
    b = a

http://sqlfiddle.com/#!3/f6984/1

The SQL-92 specification (Section 13.10, General Rules, Item 6 on page 395) states:

  • The <value expression>s are effectively evaluated for each row of T before updating any row of T.

Upvotes: 5

Related Questions