Deleting a record with a minimum identifier

Question

When I enter this query in MySQL:

DELETE FROM myTable WHERE ID = ( SELECT Min( ID ) FROM myTable ) 

The following error message appears:

 #1093 - You can't specify target table 'myTable' for update in FROM clause 

What is the problem?

What is the correct equivalent?

Answer 1

Basically in MySQL, you cannot update the table that you use in the SELECT part. For details, you can check this behavior, which is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html

In theory, every DELETE is UPDATE , so you get this error.

You can simply do the following:

 DELETE FROM myTable ORDER BY my_id LIMIT 1; 

Answer 2

try

DELETE FROM myTable ORDER BY ID LIMIT 1;

Answer 3

This is because in MySQL you cannot change the same table that you use in the SELECT part.

Read: http://dev.mysql.com/doc/refman/5.6/en/update.html

Answer 4

In MySQL, you cannot change the same table that you use in the SELECT part. This behavior is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html

Instead of a nested subquery, perform the operation in two parts, or alternatively use the simple where clause.

Try:

 $min_id = SELECT Min( ID ) FROM myTable DELETE FROM myTable WHERE ID = $min_id 

Now you will not get any errors.

Hurrah!..

Answer 5

 DELETE FROM myTable WHERE ID = (SELECT ID FROM myTable ORDER BY ID LIMIT 1) 

Answer 6

 DELETE FROM myTable ORDER BY ID ASC LIMIT 1 

Answer 7

try it

 DELETE FROM `table_name` ORDER BY id LIMIT 1;