CODEWITHSUNDEEP
sqlsql-serversql-server-2008relational-database
TelJanini
TelJanini

Reputation: 835

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

Upvotes: 10

Views: 5290

Answers (4)

Martin Smith
Martin Smith

Reputation: 453278

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.

Upvotes: 4

Allan Chua
Allan Chua

Reputation: 10175

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 :)

Upvotes: 0

user989818
user989818

Reputation:

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.

Upvotes: 5

CyberDude
CyberDude

Reputation: 8959

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

Upvotes: 2

Related Questions