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)