SQL UPDATE order of evaluation

by

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.

More Related Contents:

  1. The most efficient way to UPPER CASE two columns in a single query [closed]
  2. PL SQL UPDATE STATEMENT
  3. How can I do an UPDATE statement with JOIN in SQL Server?
  4. How do I (or can I) SELECT DISTINCT on multiple columns?
  5. SQL update from one Table to another based on a ID match
  6. SQL Logic Operator Precedence: And and Or
  7. Oracle SQL: Update a table with data from another table
  8. updating table rows in postgres using subquery
  9. how can I Update top 100 records in sql server
  10. Return pre-UPDATE column values using SQL only
  11. Casting NULL type when updating multiple rows
  12. How to write UPDATE SQL with Table alias in SQL Server 2008?
  13. Slow simple update query on PostgreSQL database with 3 million rows
  14. SQL Server Update Trigger, Get Only modified fields
  15. Use SELECT inside an UPDATE query
  16. Oracle Update Query using Join
  17. Update with Join query in Oracle
  18. SQL Server Update Group by
  19. PostgreSQL does not accept column alias in WHERE clause
  20. I want to use CASE statement to update some records in sql server 2005
  21. Bulk/batch update/upsert in PostgreSQL
  22. Postgres error updating column data
  23. Update a column of a table with a column of another table in PostgreSQL
  24. Functions with variable number of input parameters
  25. Update query using Subquery in Sql Server
  26. how to increment integer Columns value by 1 in SQL
  27. T-SQL: Using a CASE in an UPDATE statement to update certain columns depending on a condition
  28. Update rows in one table with data from another table based on one column in each being equal
  29. SQL Update woes in MS Access – Operation must use an updateable query
  30. Update a table with data from another table

Leave a Comment