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?
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)
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.