10

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?

TelJanini
  • 835
  • 9
  • 25
  • If it has to have the smarts to roll back the transaction it makes sense that it would work that way. – paparazzo Oct 13 '11 at 15:02
  • That SQL statement is valid in most every RDBMS. It's standard SQL. Think of it like that: you have an OLD row and a NEW row. All references to columns point to the OLD row. When all is done, OLD is replaced by NEW. – Erwin Brandstetter Oct 13 '11 at 15:03
  • 1
    It's vendor specific; in *MySQL* 5 you would end up with both columns containing LASTNAME ("Single-table UPDATE assignments are generally evaluated from left to right") – Alex K. Oct 13 '11 at 15:32
  • I think I would have changed the column names in the table definition instead. – idstam Oct 21 '11 at 18:49

4 Answers4

5

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.

  • By the way, its why you are not supposed to use tables with too many columns (if the table are updated constantly). –  Oct 13 '11 at 20:35
  • In SQL Server updates will generally be carried out in place unless you are updating the clustered index key and the row needs to move. – Martin Smith Oct 15 '11 at 09:51
4

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.

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

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

CyberDude
  • 8,541
  • 5
  • 29
  • 47
0

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

Allan Chua
  • 9,305
  • 9
  • 41
  • 61