Search code examples
mysqlsqlatomic

MySQL Update Causing Column Values To Change Midway Through Query

Can anyone explain why this happens in MySQL?

mysql> select * from test;
+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 1 | 1 |
+----+---+---+
1 row in set

mysql> update test set a = a + 1, b = a + 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 2 | 3 |
+----+---+---+
1 row in set

mysql>

Schema:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL,
  `a` int(10) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Solution

  • That's the expected behavior, according to the MySQL documentation:

    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.

    However, if you want to prevent it, and have the query use the original value, try running it inside a transaction.