Do UPDATE statements set all values concurrently (at the same time)?
mysql, sql
Solution
As indicated by Joe's SQL Fiddle comment, it does indeed matter what order the column updates are performed. The MySQL documentation for `UPDATE` indicates this, stating that the expression for each column update will be evaluated in the order declared, and it always uses the "current" value of a column:
If you access a column from the table to be updated in an expression, `UPDATE` uses the current value of the column. For example, the following statement sets `col1` to one more than its current value:
UPDATE t1 SET col1 = col1 + 1;
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;
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.
So for `UPDATE var SET foo=foo+1, bar=foo+1`, `foo` will be set to `foo+1`, then the `bar` update will use the newly set value of `foo` in `foo+1`.
It may also be worthwhile to note that this behavior differs from the SQL standard, which indicates that all column updates should conceptually happen "at the same time".
Problem
Very simple question yet I could not find the answer. Suppose I have table `var` with columns `foo` and `bar`, both of which are integers and currently at 0. If I run: `UPDATE var SET foo=foo+1, bar=foo+1` Then what will be the value of `bar`? 1 or 2? Does it change if I switch the order? `UPDATE var SET bar=foo+1, foo=foo+1`