CODEWITHSUNDEEP
mysqlsql
Softey
Softey

Reputation: 1491

MYSQL Select MAX Value and Delete

I have a table in my script that logs transactions and each transaction is given a unique ID number( a auto increment) and I would like to select the highest transaction ID row and delete it. I had something like 

SELECT * FROM trans WHERE (Select MAX(tranID)FROM Trans);

To get the information and 

DELETE FROM Trans WHERE (SELECT MAX(trainID));

To delete it.

Now these work but they select everything and delete every entry.

Any ideas?

Upvotes: 1

Views: 3365

Answers (3)

NoobEditor
NoobEditor

Reputation: 15871

Now these work but they select everything and delete every entry.

In this query :

DELETE FROM Trans WHERE (SELECT MAX(trainID));

After process will result to :

DELETE FROM Trans WHERE (true);

hence this is equal to => DELETE FROM Trans and thats why all your table data is deleted!

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270609

MySQL supports a LIMIT for DELETE queries. Rather than attempting to get the MAX(trainID) which you have found leads to errors when done with a subquery, specify the ORDER BY and LIMIT.

DELETE FROM Trans
ORDER BY trainID DESC
LIMIT 1

It should alternatively work to join against a subquery instead of trying to use it in the WHERE:

DELETE t.*
FROM 
  Trans t
  INNER JOIN (SELECT MAX(trainID) maxt FROM Trans) tmax ON t.trainID = tmax.maxt

Here are both version in action

Yours deletes all rows because (SELECT MAX(trainID)) evaluates as a boolean TRUE, which matches all rows

Upvotes: 3

Pankaj Gadge
Pankaj Gadge

Reputation: 2814

DELETE FROM Trans WHERE trainId = (SELECT MAX(trainID) FROM Trans );

Upvotes: 0

Related Questions