CODEWITHSUNDEEP
sqlt-sql
George Menoutis
George Menoutis

Reputation: 7250

Sequential update statements

When using multiple SETs on a single update query like

update table set col1=value1,col2=col1

is there an order of execution that will decide the outcome, when the same column is left or right of an equals sign? As far as I've tested so far, it seems when a column is used to the right of an equals as a data source, then its value is used from BEFORE it gets a new value within the same update statement, by being to the left of an equals sign elsewhere.

Upvotes: 3

Views: 740

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521979

I believe that SQL Server always uses the old values when performing an UPDATE. This would best be explained by showing some sample data for your table:

col1 | col2
1    | 3
2    | 8
3    | 10

update table set col1=value1,col2=col1

At the end of this UPDATE, the table should look like this:

col1   | col2
value1 | 1
value1 | 2
value1 | 3

This behavior for UPDATE is part of the ANSI-92 SQL standard, as this SO question discusses:

SQL UPDATE read column values before setting

Here is another link which discusses this problem with an example:

http://dba.fyicenter.com/faq/sql_server/Using_Old_Values_to_Define_New_Values_in_UPDATE_Statements.html

You can assume that in general SQL Server puts some sort of lock on the table during an UPDATE, and uses a snapshot of the old values throughout the entire UPDATE statement.

Upvotes: 3

Related Questions