SELECT and DELETE

Question

I want to select the first row in a table, order by time (ascending), then delete that row. I don't want to use two queries since there's a possibility that another client could select that row before it gets deleted (there will be several machines connected at once from different networks).

I was thinking I could do something like

SELECT * FROM `mytable` ORDER BY `time` LIMIT 1;
    DELETE FROM `mytable` ORDER BY `time` LIMIT 1

...but I got an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; DELETE * FROM pending ORDER BY time LIMIT 1' at line 1

What's the best way to do this? Thanks.

Answer 1

Re your error message (different than in your question):

DELETE * FROM pending ORDER BY time LIMIT 1

Looks like an error in your syntax. Try removing the *. That is,

DELETE FROM pending ORDER BY time LIMIT 1

Should work fine.

Answer 2

You have to create a temporary table, MySQL doesn't let you delete from a table that you are using, see this code:

insert  tmpTable
        (id)
select  id
from    YourTable yt
order by time limit 1;

delete  
from    YourTable
where   ID in (select id from tmpTable);

Answer 3

You can use sub-queries like.

delete from table where id in ( select id from table order by time limit 1);

Performance wise am not sure, how good is this solution. You might have to do a analyze and see how this works for you.