CODEWITHSUNDEEP
mysqlsql
user3796265
user3796265

Reputation: 17

Delete a row with condition on the same table in mysql

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??

Upvotes: 2

Views: 3669

Answers (4)

JGFMK
JGFMK

Reputation: 8904

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

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

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
            )

Upvotes: 1

Vipin CP
Vipin CP

Reputation: 3797

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

Upvotes: 6

Anik
Anik

Reputation: 471

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)

Upvotes: 2

Related Questions