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.