Does SQL UPDATE Statement Evaluation Order Follow a Standard, and How Do Different Databases Handle It?

SQL UPDATE Evaluation Order

In an SQL UPDATE statement, the order of evaluation determines whether subsequent expressions rely on modified values. This article explores the evaluation order in the query:

UPDATE tbl SET q = q + 1, p = q;

MySQL Evaluation Order

In MySQL, the evaluation order is "left to right." This means that the expression "q 1" is evaluated before "q" in the assignment to "p." Therefore, tbl.p will be set to the value of q before it is incremented.

SQL Standard

According to the SQL92 specifications, "[The value expressions] are effectively evaluated for each row of T before updating any row of T." However, this statement is not explicitly clear.

Implementations and Behavior

Despite the standard, implementations vary in their evaluation order. Tests have shown that the following databases set tbl.p to q:

• Oracle XE (10g)
• PostgreSQL 8.4.2
• SQLite 3.3.6
• SQL Server 2016

On the other hand, the following databases set tbl.p to q 1:

• Firebird 2.5
• InterBase 2009
• MySQL 5.0.77

MySQL Bug and Documentation Update

MySQL had previously defied the majority behavior, but a bug was raised and the documentation was updated to explicitly reflect the "left to right" evaluation order. This behavior is marked as a difference from Standard SQL.

The above is the detailed content of Does SQL UPDATE Statement Evaluation Order Follow a Standard, and How Do Different Databases Handle It?. For more information, please follow other related articles on the PHP Chinese website!