The procedure for evaluating an expression in an update-set clause in an oracle database

Question

In the table, I have columns A and B. I want to update A using the value of B, and then update B to the new value. This should be done atomically.

I'm trying something like this

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

Although this works, I cannot find documentation that guarantees me that A = B is rated first and B = 10 is rated later.

I looked at the oracle sql link of the update statement

Answer 1

You can find this in the SQL standard, which defines general rules.
Oracle certainly complies with this standard. See Here - SQL 92: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Page 393, chapter "13.9 <update instruction: positioned>", paragraph 6)

6) The effect of the expression <value> s is effectively evaluated before updating, in the line of the object. If a contains a reference to column T, then a reference to the value of this column in the object row before any value of the object row is updated.

Consider the general update syntax: <

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

Rule # 6 states that all expressions on the right side are first evaluated before updating any column in the row.
When evaluating all expressions, only the values ​​of old lines are taken into account (before updating).

Answer 2

MMO, Oracle, like any other DBMS based on the first pool of your data in the cache from the table, is then read from this cached information, so I think that when you use the field name on the left side of SET , then RDMBS reads the value of your old data ( before any changes).

Answer 3

In an RDBMS (unlike a programming language) there is no evaluation procedure; all this is done immediately. This is similar to the fact that you first set the variables to the previous value, and then use these variables:

 SET a=b, b=a 

just toggles a and b .

Warning: only MySQL does it completely wrong, as a result both parameters are set to the same value b , here you need a temporary variable, for example:

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

Answer 4

The SQL standard does not guarantee any order in an update application. I would suggest running two updates in the desired order to make sure they are in the correct order.

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