FIX MYSQL UPDATE INNER JOIN and ORDER BY QUERY and SET VARIABLE

Question

I have 2 MYSQL tables: durations and tasks.

CREATE TABLE `durations` (
  `task_type` varchar(10) NOT NULL,
  `seconds` int(11) NOT NULL
) ENGINE=InnoDB ;

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL,
  `task_type` varchar(10) NOT NULL,
  `const` int(11) NOT NULL,
  `timestamp` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `tempCol` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB ;


task_type   seconds
type1       3
type2       5

id  task_type   const   timestamp   status  tempCol
1   type1       1       10          0       0              
2   type1       2       3           0       0
3   type1       3       12          0       0
4   type1       4       5           0       0

I have a total time value: e.g 15 seconds. Starting from this value, I have to update the status of each row only if total_time - tasks.const*durations.seconds is > 0. If the update is done in a row, total_time decreases in tasks.const*durations.seconds to use in the next row. I have run the following query:

SET @tempVariable = 15;
UPDATE tasks 
INNER JOIN durations ON tasks.task_type=durations.task_type
SET status=1, tasks.tempCol = (@tempVariable:[email protected]*durations.seconds)
WHERE @tempVariable-tasks.const*durations.seconds > 0
ORDER BY tasks.timestamp ASC

I have created tempCol so that @tempVariable could be updated in each row. This query doesn't work due to Incorrect usage of UPDATE and ORDER BY. If I omit the order by clause, the query works fine, however, I need to update values ORDER by timestamp ASC.

Any suggestions to fix the query?

Thanks.

Edited. My final query based on GMB solution is:

SET @tempVariable = 15;
update tasks t
inner join (
    select t.id, (t.const * d.seconds) workload
    from tasks t
    inner join durations d on d.task_type = t.task_type
    order by t.`timestamp` asc
) t1 on t1.id = t.id
set t.status = 1, t.tempCol=(@tempVariable:[email protected])
where @tempVariable-t1.workload >= 0

Answer 1

If you are running MySQL 8.0, you can do this with window functions:

update tasks t
inner join (
    select t.id, sum(t.const * d.second) over(order by t.id) total_duration
    from tasks t
    inner join duration d on d.task_type = t.task_type
) t1 on t1.id = t.id
set t.status = 1
where t1.total_duration <= 15

In earlier versions, I would use a correlated subquery rather than variables:

update tasks t
inner join (
    select t.id, 
        (select sum(t1.const * d.second) from tasks t1 where t1.id <= t.id) total_duration
    from tasks t
    inner join duration d on d.task_type = t.task_type
) t1 on t1.id = t.id
set t.status = 1
where t1.total_duration <) 15