User Name:

User Email:

This information will only be saved for the purposes of communicating with those who have provided this information voluntarily regarding our services.We will never sell your name or email address to anyone.
© 2021 - First Crazy Developer (Abhishek Kumar)

crazydeveloper Magic of Sql Server with Update query

Magic of SQL Server

Today we will talk about magic of Sql Server. There is a scenario, we need to swap value between two columns, what will be sql query for that?

Look the following sql query and you will surprised that there is a simple way to do that in Sql without using third variable.

update tablename

set firstName=lastName,lastName=firstName


Tell me above sql query will be execute or we will get error. In other language we get error, when we try to execute this query. But in Sql this query will be executed successfully. This is magic of SQL Server. Now can you explain me, why this will happen & what is magic for that?


The reason of this magic is the way UPDATE process internally in SQL Server. Update construct with a 'pseudo-table'.


1) We need the base table. It cannot have an alias because an alias would create a working table that would be updated and then disappear after the statement is finished, thus doing nothing.


2) If we use the WHERE clause. All rows (if any!) that test TRUE are marked as a subset. If there is no WHERE clause, then the entire table is marked. The name of this set/pseudo-table is OLD in Standard SQL.


3) After that the SET clause construct a set/pseudo-table called NEW. The rows in this table are built by copying values from the columns are not mentioned from the original row to the NEW row. The columns are assigned all at once. That is, the unit of work is a row, not one column at a time.


4) The OLD subset is deleted and the NEW set is inserted. Those are the proprietary terms used in SQL Server, too. This is why

update tablename

set firstName=lastName,lastName=firstName


Swaps the values in the columns firstName and lastName. The engine checks constraints and does a ROLLBACK if there are violations.

Trying to UPDATE the temporary result of a JOIN syntax would be useless - that temporary result disappears at the end of the statement and never touches the base tables. And also both column have same datatype.


crazydeveloper Home Page 24 June 2015

Become a Fan