MySQL: delete rows returned by subquery

Question

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);

But getting an error:

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

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

Answer 1

delete from resultitem 
       where id not in (select r.id  
                  from (select r1.id, unique(r1.link) from resultitem r1) as r );

Answer 2

Try this one...

delete r from resultitem r
INNER JOIN (
       (select id 
                    from resultitem rr 
                    group by rr.link
                    having count(rr.id) >1) rr
ON r.id = rr.id;

Answer 3

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

Live example at SQL Fiddle.

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

Answer 4

DELETE resultitem
FROM resultitem
LEFT OUTER JOIN (
   SELECT MIN(id) as RowId, link
   FROM resultitem
   GROUP BY link
) as KeepRows ON
   resultitem.id = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL