CODEWITHSUNDEEP
mysqlsqlmysql-error-1093
Robert
Robert

Reputation: 8619

correlated subquery update

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)

Upvotes: 0

Views: 286

Answers (1)

manji
manji

Reputation: 47968

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

Upvotes: 4

Related Questions