Can I change a field that is used to update another field?

Question

Now this is a valid statement that is being executed, and I noticed that it is performing the desired operation. My question is: is it reliable to do this in a single statement in SQL Server? By reliable, I mean that it Foowill be set to Barits original value before the value Baris changed to null.

update SomeTable
set Foo = Bar,
    Bar = null

Please note that I am asking a question about the fundamental design of SQL Server. Therefore, I am interested in answers that go beyond the scope of observations, and cite Microsoft documentation (or the old sybase) and / or convincingly demonstrate why SQL Server works in a certain way at a low fundamental level.

Answer 1

Yes, this will behave the way you want in SQL Server. Foo will be set to Bar before Bar is set to NULL.

EDIT: Source - http://dba.fyicenter.com/faq/sql_server/Importance_of_Column_Order_in_SET_Clause.html

Answer 2

To be safe, I would split this UPDATE into two separate update statements and wrap the two UPDATE statements inside an ONE transaction.

I don't think that the order in which you mention the columns in the SET clause decides which column is updated first. Therefore, to keep matter simple, I would do the following:

BEGIN TRANSACTION;

 update SomeTable
    set Foo = Bar

 update SomeTable
   set Bar = null

COMMIT TRANSACTION;

Answer 3

SET . SQL Server . .

,

update SomeTable
set Foo = Bar,
    Bar = Foo

SQL Fiddle Demo