MySQL:删除子查询返回的行

Question

我需要从表中删除包含重复 link 列的行。我正在尝试运行:

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 中通过子查询删除行？请指教。

Answer 1

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

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

Live example at SQL Fiddle.

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