根据最大值删除行 [英] Deleting a row based on the max value

问题描述

如何构造mySQL查询以基于最大值删除行.

How can I structure a mySQL query to delete a row based on the max value.

我尝试了

WHERE jobPositonId = max(jobPostionId)

但出现错误?

推荐答案

使用:

DELETE FROM TABLE t1 
       JOIN (SELECT MAX(jobPositonId) AS max_id FROM TABLE) t2 
 WHERE t1.jobPositonId  = t2.max_id

请注意，如果存在重复，则将删除具有该jobPositonId值的行 all .

Mind that all the rows with that jobPositonId value will be removed, if there are duplicates.

关于1093错误的愚蠢之处在于，您可以通过在自引用之间放置一个子查询来解决它:

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)

说明

仅在使用UPDATE& DELETE语句，如果存在正在更新的同一表的一级子查询.这就是为什么将其放在第二级(或更深层)的子查询替代项中的原因.但这只是检查子查询-JOIN语法在逻辑上是等效的，但不会触发错误.

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.

这篇关于根据最大值删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！