CODEWITHSUNDEEP
mysql
IzzEps
IzzEps

Reputation: 582

How will MySql handle an Update that uses the field-to-be-updated in other parts of the query?

So say I have the following statement:

UPDATE time_tracking_table 
SET `end` = NOW(), `duration` = TIME_TO_SEC(TIMEDIFF(`end`,`start`)) 
WHERE `end` > NOW()

What I am trying to achieve is the following (using 10pm as an example for NOW()): "find all rows where end is later then 10pm, then update end for that row and set it to 10pm, then calculate the difference between the start of that row and it's new value (10pm) as the duration".

I have been testing the above query for a few days and it seems to work. My question is:

a) Is it reliable (or is there some sort of race condition involved here considering the simultaneous update of end and use of end in the where and timediff) and...

b) Even if it does work, is there perhaps a better way to achieve this?

Many thanks in advance for your time and expertise!

Upvotes: 1

Views: 62

Answers (1)

Uueerdo
Uueerdo

Reputation: 15961

From the MySQL docs on UPDATE...

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;

Upvotes: 3

Related Questions