0

The table contains the columns id, timestamp (eg. 2013-09-23 12:10:53), activity and I want to add another column duration which would contain the duration of each activity (ie. the difference between the next row time stamp and the current one).

I've tried this query:

UPDATE `MyTable` this SET `duration`= 
        (SELECT DATEDIFF(next.`timestamp`, curr.`timestamp`) 
        FROM `MyTable` curr
        JOIN `MyTable` next
        ON next.`id` = curr.`id`+1
        WHERE this.`id` = curr.`id`)

And got this error: 

#1093 - You can't specify target table 'this' for update in FROM clause

How can I go about doing that?

FloIancu
  • 2,685
  • 8
  • 29
  • 34

2 Answers2

1

Instead, use a join:

UPDATE MyTable this left join
       MyTable next
       ON next.id = this.id + 1
    SET this.duration = DATEDIFF(next.timestamp, this.timestamp) ;

I think the error is self-explanatory. The usual solution in MySQL is to convert the update to use joins instead of correlated subqueries.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This worked best, although it still had some errors in it (I attribute those to the faulty design of the table which isn't mine...). Thank you! – FloIancu Dec 28 '14 at 13:38
0

Some reference for you

You can't specify target table for update in FROM clause

http://dev.mysql.com/doc/refman/5.0/en/update.html

UPDATE `MyTable` this SET `duration`= 
    (SELECT DATEDIFF(next.`timestamp`, curr.`timestamp`) 
    FROM (SELECT * from MyTable) curr
    JOIN (SELECT * from MyTable) next
    ON next.`id` = curr.`id`+1
    WHERE this.`id` = curr.`id`);
Community
  • 1
  • 1
Bijay Luitel
  • 9
  • 3
  • Tried it and I get the same error. I also removed `this` from the last row and had the same result. – FloIancu Dec 27 '14 at 12:43