Updating one column with other's value in same table

Question

Please consider this sql statements

Create table abc 
(A int,
B int
)

insert into abc values (1,2)

Both of the below statements do the same thing. Why?

update abc 
set A = B,
B =0
where A=1

and

update abc 
set B =0,
A = B
where A=1

I was thinking that in the later statement B columns value is set first and then A columns' value is set to B's value

Answer 1

No. Single update statements are atomic and there is no order in their individual parts.

Both of these:

update abc set A = B, B = 0 where A=1
update abc set B = 0, A = B where A=1

do exactly the same thing because the two assignments are considered to happen concurrently.

In other words, B on the right side of = is the old value of B.

---

Addendum: How a DBMS implements this behaviour depends on the cleverness of those writing the DBMS.

For example a DBMS might attempt to lock all the rows where A is 1 then, once that's done, go through and execute A = B, B = 0 (in that order because the execution engine deems that will satisfy concurrency, setting A to B before changing B) on each of those rows.

A statement like set A = B, B = A would require somewhat more intelligence but it could do that easily enough by saving the current row first and using values there to set values in the new row, something like:

read in oldrow
copy oldrow to newrow
newrow.A = oldrow.B
newrow.B = oldrow.A
write out newrow

Then it will unlock all the rows.

That's just one option. A very dumb DBMS may just lock the entire database file although that wouldn't make for very intelligent concurrency.

A single-user, single-thread DBMS doesn't have to care about concurrency at all. It would lock absolutely nothing, just going through each relevant row, making the changes.

Answer 2

SQL does not evaluate values by order of field. The statements are identical as far as SQL is concerned.

The update happens in one step (atomic), not several ordered ones.

What happens is that SQL accesses each row in the table, then updates A to the current value of B and at the same time updates B to be 0.

If it helps you understand, you can think of it as what happens in an update trigger, which has access to the current value of the table in the DELETED logical table and to the new values in the INSERTED logical table.

Answer 3

Now I understand that, there are two logical tables i.e DELETED and INSERTED that come into picture when we run an Update statement.

So the value being set to A column is retrieved from B column of DELETED table and the value of B which is being set to 0 is the one in the INSERTED table.

Answer 4

Variation on @Ismail 's answer: logically speaking, an update is a delete and an insert that are committed together, that's why you get a row in each of the logical tables deleted and inserted respectively. The unit of work here is the row: when a row is updated it is deleted and re-inserted using the new values (if you want to know which column values actually change you have to work it out yourself).

A useful aide-mémoire is that

UPDATE MyTable
   SET A = B, B = A;

will transpose the columns' values.