SQL update execution order matters?

Question

I was always of the understanding that during a SQL UPDATE the existing values would remain until the entire update statement was complete, but I am seeing different behaviour in practice.

Inventory
-----------------------------
date        base  flexible
2014-05-01  5     10
2014-05-02  5     10

UPDATE Inventory SET base = GREATEST(0, base - 7), flexible = flexible - GREATEST(0, (7 - base)) WHERE date = '2014-05-01'

UPDATE Inventory SET flexible = flexible - GREATEST(0, (7 - base)), base = GREATEST(0, base - 7) WHERE date = '2014-05-02'

Inventory
-----------------------------
date        base  flexible
2014-05-01  0     3
2014-05-02  0     8

In the first example it seems that base is updated to zero with the first expression, causing the second expression to evaluate incorrectly (7 - 0) instead of (7 - 5).

Can anyone explain what is going on here?

Answer 1

MySQL breaks the standard a bit;

It updates columns using the current field value considering the field order in the update instead of following the SQL standard which uses the field value from before the update statement.

From the docs;

If you access a column from the table to be updated in an expression, UPDATE uses the current value of the column. The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;