Geek Answers Handbook

SELECT and DELETE

I want to select the first row in the table, sort by time(ascending), and then delete this row. I do not want to use two queries, since there is a chance that another client may select this line before it is deleted (several computers will be connected from different networks at once).

I thought I could do something like

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

... but I have an error:

You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to; DELETE * FROM pendingORDER BY timeLIMIT 1 'on line 1

What is the best way to do this? Thank.

+5
source share
3

( ):

DELETE * FROM pending ORDER BY time LIMIT 1

. *.

DELETE FROM pending ORDER BY time LIMIT 1

.

+4

, MySQL , , . :

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

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

You can use subqueries such as.

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

Performance is not sure how good this solution is. You may need to do an analysis and see how it works for you.

+1
source

More articles:


All Articles