By Robert de Klerk


2010-09-01 18:16:49 8 Comments

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?

4 comments

@sdfor 2012-06-07 16:03:20

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?

@Konerak 2010-09-01 19:20:49

DELETE FROM table ORDER BY jobPositonId DESC LIMIT 1

@Daniel Katz 2017-09-28 11:51:44

Perfect! Deleted all rows at once in my self-referencing table. (without the limit 1 of course)

@OMG Ponies 2010-09-01 18:27:22

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 2010-09-01 18:51:22

What does that error message actually mean?

@OMG Ponies 2010-09-01 19:11:16

@Robert de Klerk: See update to my answer to explain the behaviour.

@el_quick 2010-09-01 18:28:00

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)

@OMG Ponies 2010-09-01 18:28:25

MySQL error 1093 - can't specify target table for update in FROM clause

@Thomas 2010-09-01 18:31:38

Why would you even need the LIMIT clause if you are using Max with no correlation or group by?

@Robert de Klerk 2010-09-01 18:46:51

Same error MySQL error 1093 - can't specify target table for update in FROM clause

@el_quick 2010-09-01 19:51:12

Sorry for my bad info, I didn't have time to test it, +1 for Konerak, he post the correct answer ;)

Related Questions

Sponsored Content

20 Answered Questions

[SOLVED] How to delete duplicate rows in SQL Server?

15 Answered Questions

[SOLVED] UTF-8 all the way through

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

45 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

15 Answered Questions

[SOLVED] MySQL Error 1093 - Can't specify target table for update in FROM clause

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

27 Answered Questions

[SOLVED] SQL select only rows with max value on a column

34 Answered Questions

[SOLVED] Fetch the row which has the Max value for a column

28 Answered Questions

2 Answered Questions

[SOLVED] Delete all Duplicate Rows except for One in MySQL?

  • 2011-01-13 20:58:38
  • Highway of Life
  • 434058 View
  • 401 Score
  • 2 Answer
  • Tags:   mysql sql duplicates

Sponsored Content