Deleting a row based on the max value

Question

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?

I would advise against that. How come you don't know a certain id of the row to be deleted? — Your Common Sense, Sep 01 '10 at 18:27
@Common Sense, as the question states, the desired row to be deleted is the row which is defined by a max value (rather than a row defined by an id). This would accomplish in one query what you might propose to do in two? That is, query for the id of the max row; then delete that row. — Aaron, Oct 15 '15 at 05:21

score 18 · Answer 1 · answered Sep 01 '10 at 19:20

18

DELETE FROM table ORDER BY jobPositonId DESC LIMIT 1

answered Sep 01 '10 at 19:20

Konerak

37,118
11
93
114

Perfect! Deleted all rows at once in my self-referencing table. (without the limit 1 of course) – Daniel Katz Sep 28 '17 at 11:51

OMG Ponies · Accepted Answer · 2010-09-01T19:10:45.460

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.

@Robert de Klerk: See update to my answer to explain the behaviour. — OMG Ponies, Sep 01 '10 at 19:11

score 0 · Answer 3 · answered Jun 07 '12 at 16:03

0

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?

answered Jun 07 '12 at 16:03

sdfor

5,786
11
47
59

score 0 · Answer 4 · answered Sep 01 '10 at 18:28

0

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)

answered Sep 01 '10 at 18:28

el_quick

4,390
11
38
51

MySQL error 1093 - can't specify target table for update in FROM clause – OMG Ponies Sep 01 '10 at 18:28
1

Why would you even need the LIMIT clause if you are using Max with no correlation or group by? – Thomas Sep 01 '10 at 18:31
Same error MySQL error 1093 - can't specify target table for update in FROM clause – Robert de Klerk Sep 01 '10 at 18:46
Sorry for my bad info, I didn't have time to test it, +1 for Konerak, he post the correct answer ;) – el_quick Sep 01 '10 at 19:51

Deleting a row based on the max value

4 Answers4

Explanation

Linked

Related