How SQL Database Engine Swaps Two Column Values

database-engineupdate

I want to swap values of two columns in a table, And I found that in SQL we can do that by using Update:

update the_table set first_name = last_name, last_name = first_name;

It works But I wonder How SQL can do that without overwrite data in a column of other column?

Best Answer

@mustaccio has answered how this is physically implemented.

The logical specification is as follows:

The columns on the right side of the = in an UPDATE SET statement must come from the values before the update is applied. Therefore it is simple to swap the values, because the right side always refers to the old values.

Another way to see why is to consider the equivalent rewriting of your statement:

update the_table 
set (first_name, last_name) = (last_name, first_name);

This is not implemented by all SQL products but it is equivalent according to the SQL standard and where it has been implemented, it works exactly as yours.

As to whether it is guaranteed: yes it is. It is mandated by the SQL specification, and as far as I know, most DBMSs implement this requirement (MySQL and derived/forked products eg MariaDB are an exception and do not implement the standard correctly in this case).

Related Question