CODEWITHSUNDEEP
mysqlsqlmaxsql-deletemysql-error-1093
Robert de Klerk
Robert de Klerk

Reputation: 624

Deleting a row based on the max value

How can I structure a mySQL query to delete a row based on the max value.

I tried 

WHERE jobPositonId = max(jobPostionId)

but got an error?

Upvotes: 11

Views: 43170

Answers (4)

sdfor
sdfor

Reputation: 6458

This works:

SELECT @lastid := max(jobPositonId ) from t1; 
DELETE from t1 WHERE jobPositonId = @lastid ;

Other than going to the database twice, is there anything wrong with this technique?

Upvotes: 0

Konerak
Konerak

Reputation: 39773

DELETE FROM table ORDER BY jobPositonId DESC LIMIT 1

Upvotes: 21

OMG Ponies
OMG Ponies

Reputation: 332771

Use:

DELETE FROM TABLE t1 
       JOIN (SELECT MAX(jobPositonId) AS max_id FROM TABLE) t2 
 WHERE t1.jobPositonId  = t2.max_id

Mind that all the rows with that jobPositonId value will be removed, if there are duplicates.

The stupid part about the 1093 error is that you can get around it by placing a subquery between the self reference:

DELETE FROM TABLE
 WHERE jobPositonId = (SELECT x.id
                         FROM (SELECT MAX(t.jobPostionId) AS id 
                                 FROM TABLE t) x)

Explanation

MySQL is only checking, when using UPDATE & DELETE statements, if the there's a first level subquery to the same table that is being updated. That's why putting it in a second level (or deeper) subquery alternative works. But it's only checking subqueries - the JOIN syntax is logically equivalent, but doesn't trigger the error.

Upvotes: 16

el_quick
el_quick

Reputation: 4756

DELETE FROM `table_name` WHERE jobPositonId = (select max(jobPostionId) from `table_name` limit 1)

OR

DELETE FROM `table_name` WHERE jobPositonId IN (select max(jobPostionId) from `table_name` limit 1)

Upvotes: 0

Related Questions