0

Suppose we have a table mytable with columns id, a, b and there is a row with values (123, 'foo', 'bar').

I perform this query:

UPDATE mytable SET b=a, a='boom' WHERE id=123

I'd expect the two assignments to be performed in the given order, and hence the resulting row to be (123, 'boom', 'foo'), but is this guaranteed?

Or is the order of execution undefined and hence theoretically possible that the optimizer for whatever reason decides to execute them in reverse order, which would end up with (123, 'boom', 'boom')?

MySQL documentation doesn't explicitly state that the assignments are executed in the order they appear in the query.

matteo
  • 2,934
  • 7
  • 44
  • 59
  • I would assume nothing, this may be something that changes in the future and I would hate it to be something that takes seconds to write code which assigns both values and comes back and stops working when a software update is done. – Nigel Ren Jul 03 '17 at 15:38

1 Answers1

0

Mysql UPDATE documentation says no

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 to be safe in the case you're updating one column with the value of another, i'd split the UPDATE statement into a separate query:

-- do all the fixed value updates here
UPDATE mytable SET a='boom', x='whatever' WHERE id=123; 
-- do "relational" updates here, after the values have been updated
UPDATE mytable SET b=a                    WHERE id=123;
Alex Tartan
  • 6,736
  • 10
  • 34
  • 45