Switching the values of two fields/columns in SQL Server without third variable

As a result of a mistake during an import to the a test table called CUSTOMERS, I found myself needing to switch the values of two columns in SQL Server.

I mistakenly imported the respective values for LASTNAME and FIRSTNAME into the opposite fields (i.e. the customer last name was imported into first name, and vice versa). To remedy this, I ran the following query in SQL Server 2008 R2, of course not expe开发者_如何学Ccting it to work:

UPDATE CUSTOMERS
SET LASTNAME=FIRSTNAME, FIRSTNAME=LASTNAME

Surprisingly, it worked! The limited programming experience I've had (high school, a few college courses) always followed the paradigm that switching two values required the presence of a third variable to "hold" the value of one of the initial values. In other words, I expected to have to run the following query:

UPDATE CUSTOMERS
SET SOMEOTHERFIELD = LASTNAME
SET LASTNAME = FIRSTNAME
SET FIRSTNAME = SOMEOTHERFIELD

Is this behavior only seen in SQL Server 2008 R2, or does this represent some other underlying facet of relational theory that I'm missing?

Its because the way a update works:

In the set clause are construct a pseudo-table. The rows in this table are build by copying values from the columns that are not mentioned from the original row to a new row. The columns are assigned all at once. That is, the unit of work is a row, not one column at a time. The last step is to delete the olds rows and insert the new rows. Internally, a update is select, delete, insert.

This is for the same reason that you can't define a column alias in a SELECT list then reference the alias elsewhere in the same SELECT list. To quote Joe Celko

Things happen "all at once" in SQL, not "from left to right" as they would in a sequential file/procedural language model

Expressions that appear in the same logical query processing phase are evaluated as if at the same point in time. For more about all–at–once operations see this link.

I suppose the source values are prepared first (and thus stored in an internal third holder) and applied afterward.

SQL Update statements are expected to simultaneously update the columns specified in the UPDATE Statement in most DBMS. It's great to learn things by experience :)