views:

68

answers:

1

I have decided in my table that I no longer want to record start and finish times, but rather just a start time and a duration in minutes. How can I update my table so that my new column has its values inserted based on the existing data? my attempt below yields the error:

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

UPDATE lesson 
SET duration = 
    (SELECT TIME_TO_SEC(TIMEDIFF(finish_time,start_time))/60 
     FROM lesson AS l 
     WHERE l.id = lesson.id)
+4  A: 

You dont have to do that because your updating a column with values of other columns in the same row, do just:

UPDATE lesson 
SET duration = TIME_TO_SEC(TIMEDIFF(finish_time,start_time))/60
najmeddine
D'oh - looks so obvious now!
Robert