mysql - 删除具有相同列值的记录？

Question

好吧，我有一些具有相同 address_id 记录的表，我想将它们全部删除，或者（如果可能的话）在第一个记录之后全部删除，例如：

id | person_id | address_id
1       80            3
2       226           3
3       31            5
4       46            9
5       11            9
6       123           9

在这种情况下，我想删除 id (2, 5,6) 的记录。

这是我的查询，但我收到错误：

您不能在 FROM 子句中指定目标表 'person_address' 进行更新

DELETE FROM person_address WHERE id IN (
SELECT address_id
FROM person_address
WHERE person_address.person_id < 100
GROUP BY address_id
HAVING count(*) > 1
)

选择工作正常。

标签： mysql

Answer 1

您的选择似乎也不正确。你可以试试这样的 -

delete from person_address
 where id not in (select *
                    from (SELECT min(id)
                            FROM person_address
                           WHERE person_address.person_id < 400000
                           GROUP BY address_id) temp)
   and person_id < 400000