SQL UPDATE: Does `p = q` Reflect the Original or Updated `q` Value?

SQL UPDATE Order of Evaluation

In an SQL UPDATE statement, the order in which expressions are evaluated can significantly impact the outcome. Let's examine the following query:

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

Here, the question arises: Will "tbl"."p" be set to q or q 1? Is the evaluation order governed by an SQL standard?

Implementations Vary

Despite the lack of clarity in the SQL standard, different database implementations handle evaluation order differently. Tests have revealed varying behaviors across Firebird, InterBase, MySQL, Oracle, PostgreSQL, SQLite, and SQL Server. In most cases, the value of "p" is set to q 1, indicating a left-to-right evaluation.

Standard Interpretation

However, an interpretation of the SQL92 specifications suggests that the standard intended for expressions to be evaluated before any rows are updated. This would result in "p" being set to q.

MySQL's Unique Approach

MySQL stands out as the only tested database that does "see" the new values during evaluation. This behavior differs from the majority of other implementations and may result in unexpected outcomes.

Conclusion

While the SQL standard lacks explicit guidance on evaluation order, it is generally assumed that expressions should be evaluated before updates are applied. However, implementations vary, and developers should be aware of potential discrepancies. In cases where evaluation order is critical, it is recommended to use intermediate variables to explicitly define the desired order of operations.

The above is the detailed content of SQL UPDATE: Does `p = q` Reflect the Original or Updated `q` Value?. For more information, please follow other related articles on the PHP Chinese website!