MySQL:删除子查询返回的行 [英] MySQL: delete rows returned by subquery

问题描述

我需要删除表中存在重复 link 列的行.我正在尝试运行:

I need to remove rows where there is a duplicate link column from a table. I'm trying to run:

delete from resultitem 
       where id in (select r.id 
                    from resultitem r 
                    group by r.link
                    having count(r.id) >1);

但出现错误:

ERROR 1093 (HY000): You can't specify target table 'resultitem' for update in FROM clause

是否可以在没有临时表的情况下通过 MySQL 中的子查询删除行?请指教.

Is this possible to remove rows by subquery in MySQL without a temporary table? Please advise.

推荐答案

这应该删除每个 link 除了最低的 id 之外的所有内容:

This should delete all but the lowest id per link:

delete  ri1
from    resultitem as ri1
inner join
        resultitem as ri2
on      ri1.link = ri2.link
        and ri1.id > ri2.id

SQL Fiddle 的实例.

要删除所有重复链接，不留下任何重复链接，请删除 和 ri1.id >ri2.id.

To remove all duplicate links, leaving none of the duplicates behind, remove the and ri1.id > ri2.id.

这篇关于MySQL:删除子查询返回的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！