0

So, I am trying to update the status_modified_time only if the status has changed, else keep it the same.

UPDATE table SET status = <new_status>, 
status_modified_time = IF(status = <new_status>, status_modified_time, now())  WHERE id = <id>

this query makes status_modified_time = status_modified_time

UPDATE table SET status = <new_status>, 
status_modified_time = IF(status = <old_status>, status_modified_time, now())  WHERE id = <id>

this query makes status_modified_time = now() is it that mysql is updating the status field first and then checking the condition??

apesa
  • 12,163
  • 6
  • 38
  • 43
nipun
  • 103
  • 1
  • 11

1 Answers1

1

Left to right order of evaluation! SQL UPDATE order of evaluation

I checked my query by updating the modified_time first.

Community
  • 1
  • 1
nipun
  • 103
  • 1
  • 11
  • 1
    Your answer is correct. The behavior of MySQL is not standard. SQL isn't supposed to have an order -- everything conceptually is supposed to happen at the same time, so referencing a column in an `UPDATE` query *should* reference the old value... but it doesn't. It is indeed done left-to-right. – Michael - sqlbot May 25 '16 at 00:56