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 expecting 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?