Search code examples
sql-servert-sql

Using the columnA value to update columnB while also updating columnA in the same statement

For example:

UPDATE Table1 
SET Column1 = Column2, Column2 = Column3

Is this guaranteed to always result in an update like this:

before:

Column1 Column2 Column3
10 200 3000

after:

Column1 Column2 Column3
200 3000 3000

A quick experimentation suggests it works, but I couldn't find anything on MSDN that definitively says it would.

Solution

  • Yes, the result will be as desired. SQL Server performs the update as a set-based operation so the update logically happens all at once. One can even reverse the column order to achieve the same results:

    UPDATE Table1 SET Column1 = Column2, Column2 = Column3;
UPDATE Table1 SET Column2 = Column3, Column1 = Column2;