CODEWITHSUNDEEP
sqlsql-server-2008swap
SHAKIR SHABBIR
SHAKIR SHABBIR

Reputation: 1295

SQL Column Swap Behavior

I'm swapping column values in a table using the following statement:

UPDATE SwapTable
SET ValueA=ValueB
   ,ValueB=ValueA

This works and the values do get swapped, as can be verified by this SQL Fiddle.

However, if we did such thing in (mostly any) other language, we would end up with both ValueA and ValueB having identical values.

So my question is why/how this works in SQL.

Upvotes: 1

Views: 149

Answers (1)

tsohr
tsohr

Reputation: 915

You can just see the execution plan.

  1. Select all the rows from the table and make it as a row set.
  2. Open a transaction
  3. Update the table referenced (SwapTable) with corresponding row address, from the old values read from the row set to the field reference.
  4. Commit -- done updating.

Upvotes: 4

Related Questions