SQL Update Order of Evaluation

SQL UPDATE order of evaluation

MySQL does "left to right" evaluation and does "see" the new values.
(Tested on 5.0.45-community-nt-log MySQL Community Edition)

Furthermore, from the MySQL manual: "Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order."

Now, "generally" is quite vague and "no guarantee" is very bad given that the order of evaluation is important.

So, in order to answer the question: IS the behaviour specified by "the SQL standard" or is it just a convention?

---

UPDATE: Got hold of the SQL92 specs which state at "13.10 update statement: searched" item "6) The (value expression)s are effectively evaluated for each row of T before updating any row of T."

IMHO not absolutely unambiguous, but enough to consider that the STANDARD is NOT to "see" the results of your own update.
Considering your example, the way Oracle, PostgreSQL and Interbase do it.

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

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.

Order of execution of SQL UPDATE while updating multiple values?

I don't think it will matter UPDATE will read the current row and do the update upon it based on the existing values, and not the ones that are in the update.

So in both SET operations, the original value of rating_count will be used.

SQL Conceptual Order Evaluation

A select query is evaluated, conceptually, in the following order:

1. The from clause
2. The where clause
3. The group by clause
4. The having clause
5. The select clause
6. The order by clause

This is "conceptual" processing and explains some of the scoping rules of SQL. The way queries are executed in practice may differ.

SQL Server documentation explains this ordering here.

SQL 2000: Variables in update statements and order of evaluation

You could rid yourself the worry about order of evaluation with this:

UPDATE tbl
SET tbl.a = s.theSum,
    tbl.b = s.theSum / 2
FROM tbl
INNER JOIN (
    SELECT x, SUM(amount) AS thesum
    FROM anothertable
    GROUP BY x
) s ON s.x = tbl.y

---

Related Topics