MySQL update value using maximum date

Question

I have this request, I want to be able to update the exec value to TRUE when my program finishes executing the request and saves it in my database to use it as a queue, but I get an error message every time I execute this request .

UPDATE motor SET exec=1 where time=(SELECT max(time) FROM motor WHERE exec=0); 

Error:

ERROR 1093 (HY000): You cannot specify the target engine table for updating in the FROM clause

How can i do this?

Answer 1

This is because your UPDATE can run a bike.

Use this code instead:

 UPDATE motor SET exec = 1 WHERE exec = 0 ORDER BY time DESC LIMIT 1; 

Answer 2

You must do this as two separate requests. Also, why don't you have your primary engine key tracking program that he is working on in the queue and updates based on this?

Answer 3

You must assign the value that you get from the SELECT statement to a variable:

 DECLARE varTime datetime; SELECT varTime := max(time) FROM motor WHERE exec=0; UPDATE motor SET exec=1 where time= varTime 

I am not too sure of the syntax since I mainly work on MS SQL Server, but I know that it should be the same.

Answer 4

Your accepted answer is correct. Is it also possible to get the same result:

 UPDATE motor inner join (SELECT max(time) as time FROM motor WHERE exec=0) mx on motor.time = mx.time SET motor.exec=1; 

The only difference is that if there is more than one row with the same maximum value, LIMIT 1 will update only one of these rows, while this will update everything.