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.

The UPDATE does not see the results of its work.

p will be set to q as of before update.

The following code will just swap the columns:

DECLARE @test TABLE (p INT, q INT)

INSERT
INTO    @test
VALUES  (2, 3)

SELECT  *
FROM    @test

p    q
---  ---
  2    3

UPDATE  @test
SET     p = q,
        q = p

SELECT  *
FROM    @test

p    q
---  ---
  3    2