3

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
    ;
Schmalls
  • 1,434
  • 1
  • 19
  • 19
  • 1
    I'm not sure this is specified the same in all SQL implementations - see this related issue in Teradata which allows early re-use of derived columns: http://stackoverflow.com/questions/2458700/early-or-re-ordered-re-use-of-derived-columns-in-a-query-is-this-valid-ansi – Cade Roux May 01 '12 at 19:31

1 Answers1

5

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.
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Is this documented somewhere or just something that is known? – Schmalls May 01 '12 at 19:16
  • @Schmalls: "Is this documented somewhere?" Well, if it wasn't documented before, it is now. :) I *think* it's the behaviour required by the SQL standard, but I don't have a copy of it on hand that I can check. – Mark Byers May 01 '12 at 19:27
  • 3
    I found it in the [SQL-92 specification](http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt). It is Section 13.10, General Rules, Item 6 on page 395. – Schmalls May 01 '12 at 19:52
  • @Schmalls: +1 Good find. I added a quote from your link in my answer. – Mark Byers May 01 '12 at 19:56