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