CODEWITHSUNDEEP
sqlsql-server
CodexArcanum
CodexArcanum

Reputation: 4016

Does ordering matter for fields in a SQL update?

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?

Upvotes: 4

Views: 103

Answers (3)

Thomas
Thomas

Reputation: 64655

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

Upvotes: 4

egrunin
egrunin

Reputation: 25073

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.

Upvotes: 6

Jason Kester
Jason Kester

Reputation: 6031

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?

Upvotes: 0

Related Questions