7

In the following statement, will f1 always get the value that f2 used to have? Or will f2 sometimes get updated first and f1 winds up with NULL? I am under the impression that the new values are not available within the statement, that f2 has the old value while processing the record, but I can't find an authoritative place that says this.

UPDATE x
SET
    x.f1 = x.f2,
    x.f2 = NULL
Craig Celeste
  • 12,207
  • 10
  • 42
  • 49
  • Does this answer your question? [Updating Column Based On Another Updated Column](https://stackoverflow.com/questions/10402913/updating-column-based-on-another-updated-column) – Vadzim May 10 '20 at 00:36

2 Answers2

6

Conceptually the operation happens "all at once" so it will use the "before" values

Indeed 

UPDATE x
SET
 x.f1 = x.f2,
 x.f2 = x.f1

would also work fine to swap the two column values.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
2

f1 will always get f2's previous value before the UPDATE.

Technically speaking the record is deleted, and reinserted. So SQL will work out what the new record should be, then delete the current record, and insert the new record afterwards.

This article regarding SQL Triggers may help explain:

The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

http://msdn.microsoft.com/en-us/library/ms191300.aspx

Curtis
  • 101,612
  • 66
  • 270
  • 352
  • What do you mean "Technically speaking" the record is deleted, and reinserted? SQL Server can do in place updates. And TBH I simply don't see the relevance of triggers here. – Martin Smith Jul 25 '12 at 16:09
  • @MartinSmith See my quoted text. I was under the impression during an UPDATE records are deleted and inserted? – Curtis Jul 25 '12 at 16:10
  • Only in some circumstances. e.g if the clustered index key column is updated and so the row needs to move. – Martin Smith Jul 25 '12 at 16:11
  • @MartinSmith Ah okay I was under the impression this happened every time, cheers for enlightening me. I'll remove my answer. – Curtis Jul 25 '12 at 16:12
  • @MartinSmith If this is the case, do rows only get deleted/reinserted when the index key is changed, and when a SQL Trigger is linked to the table? – Curtis Jul 25 '12 at 16:15
  • A blog post looking at in place updates [is here](http://sqlinthewild.co.za/index.php/2011/06/21/are-all-updates-split-into-delete-insert/). I would need to test to be sure but AFAIK despite what BOL says in your quote an `UPDATE` on a table with a trigger won't actually be turned into a physical delete / insert on the table. – Martin Smith Jul 25 '12 at 16:21
  • Thanks @Curt. I appreciate the detail and link. – Craig Celeste Jul 25 '12 at 16:38