Does ordering matter for fields in a SQL update?

Question

It occurs to me that if you have fields dependent on each other in an update statement, I'm not sure that one can guarantee the ordering (or that one needs to!).

As an example, say you had the following Update:

UPDATE Table
SET NewValue = OldValue, OldValue = NULL

Would NewValue always update first, then OldValue be nullified? Or is the state of a row (or set, or table, etc) immutable during the processing so that all the changes aren't committed until after the changes have been calculated?

Answer 1

Why would you not simply run this as two separate queries?

begin transaction
UPDATE Table
SET NewValue = OldValue

UPDATE Table
SET OldValue = NULL
commit

Or is this homework?

Answer 2

A new virtual row is created, then it replaces the existing row atomically. You have access to all the existing values until the data is committed.

Edit This is not an unusual situation, by the way.

Answer 3

Yes, the system will update NewValue to the value that existed in OldValue prior to the execution of the query and then set OldValue to null. In fact, you can swap values like so:

UPDATE Table
SET NewValue = OldValue, OldValue = NewValue