Not registered yet?
Register now! It is easy and done in 1 minute and gives you access to special discounts and much more!
By Robert de Klerk
How can I structure a mySQL query to delete a row based on the max value.
WHERE jobPositonId = max(jobPostionId)
but got an error?
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?
DELETE FROM table ORDER BY jobPositonId DESC LIMIT 1
Perfect! Deleted all rows at once in my self-referencing table. (without the limit 1 of course)
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)
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.
What does that error message actually mean?
@Robert de Klerk: See update to my answer to explain the behaviour.
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)
MySQL error 1093 - can't specify target table for update in FROM clause
Why would you even need the LIMIT clause if you are using Max with no correlation or group by?
Same error MySQL error 1093 - can't specify target table for update in FROM clause
Sorry for my bad info, I didn't have time to test it, +1 for Konerak, he post the correct answer ;)