Order of evaluation of expression in the update-set-clause in oracle database

Question

In a table I have column A and B. I want to update A using the value of B and then update B to a new value. This has to be done atomically.

I am trying something like this

-- Intially A = 1, B = 2
UPDATE T SET A = B, B = 10 WHERE ID = 1;
-- Now A = 2, B = 10

Though this is working, I am unable to find documentation which guarantees me that A = B is evaluated first and B = 10 is evaluated later.

I looked through the oracle sql reference of the update statement

Answer 1

You can find this in SQL standard, which defines general rules.
Oracle certainly conforms to this standard.

See here - SQL 92: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Page 393, chapter "13.9 <update statement: positioned>", point 6)

6) The <value expression>s are effectively evaluated before updat- ing the object row. If a contains a reference to a column of T, then the reference is to the value of that column in the object row before any value of the object row is updated.

Consider a general update syntax:<

UPDATE .... 
   SET <object column 1> = <value expression 1>,
       <object column 2> = <value expression 2>,
       ......
       <object column N> = <value expression N>;

The rule #6 says that all expressions on right side are evaluated first, before updating of any column in the row.
Only old row's values (before the update) are considered while evaluating all expressions.

Answer 2

In a RDBMS (unlike a programming language) there's no order of evaluation, it's all done at once. It's like you set variables to the previous value first and then use those variables:

SET a=b, b=a

simply switches a and b.

Warning: Only MySQL does it totally wrong, resulting in both set to the same b value, here you'll need a temp variable like:

SET temp=b, b=a, a = temp