SQL: Exchange column values

Question

Want to clear some concepts about SQL internals.

Suppose I have a table as:

---------tblXY-----------
X int
Y int

Now it has records as:

X Y
---
1 4
2 3
3 2
4 1

And I want the resulting table to be:

X Y
---
4 1
3 2
2 3
1 4

So I wrote the query as:

UPDATE tblXY   
 SET [X] = Y
 ,[Y] = X

and got the required result.

But how did it happened? I mean I'm setting X's value as Y's current value and at the very moment I'm setting Y's value as X's.

Answer 1

It's because the operations are a single atomic action - the current values of X and Y are read first before any of the assignments are done.

So it's not so much:

for every row:
    set x = y
    set y = x

but more like:

for every row:
    set tmpx = x
    set tmpy = y
    set x = tmpy
    set y = tmpx

Keep in mind that's just the conceptual view. It's likely to be much more efficient under the covers.

Without that, you'd have to store the temporary yourself for every row, or just rename the columns :-)