Updating Column Based On Another Updated Column
sql-server, sql-server-2008, t-sql
Solution
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.
Problem
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 ; ```