Delete a row with condition on the same table in mysql

Question

I have to delete a row with max(id) from a table in MySQL. I am using query::

 DELETE 
 FROM master 
 WHERE id=(
 SELECT MAX(id) 
 FROM master)

but getting error No. 1093.

Can anybody please help me??

Answer 1

You can't specify target table for update in FROM clause.

you can delete the last row as mentioned below.

 DELETE FROM master ORDER BY id DESC LIMIT 1

Answer 2

You cant modify the same table from which you are selecting the data in subquery.

Try this -

DELETE m.*
FROM master m 
WHERE id IN (SELECT id_temp from(
SELECT MAX(id) as id_temp 
FROM master) x)

Answer 3

You can't specify target table for Delete in FROM clause

Try this

DELETE FROM master 
WHERE id IN (SELECT A.MAXid FROM 
               (SELECT MAX(id) as MAXid FROM master) A
            ) 

Answer 4

DELETE FROM Test WHERE id IN(SELECT MAX(id) FROM Test);

This seems to be cross SQL vendor friendly. The LIMIT option in MySQL becomes this in SQL SERVER.

 DELETE FROM Test WHERE id IN(SELECT TOP 1 id FROM Test ORDER BY id DESC );

See here