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?


@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


@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


       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:

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


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)


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

43 Answered Questions

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

17 Answered Questions

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

14 Answered Questions

[SOLVED] UTF-8 all the way through

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

15 Answered Questions

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

14 Answered Questions

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

2 Answered Questions

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

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

Sponsored Content