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.

It looks like your trying to remove all entries where there is a duplicate, not just the duplicate entries. Is that your intention? — podiluska
– podiluska, Commented Sep 12, 2012 at 8:38

Andomar · Accepted Answer · 2012-09-12 08:47:44Z

4

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.

edited Sep 12, 2012 at 8:47

answered Sep 12, 2012 at 8:40

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Maksym Polshcha Over a year ago

The same issue: You can't specify target table 'r1' for update in FROM clause

Andomar Over a year ago

MySQL syntax is always surprising. The edited version should work, tested on SQL Fiddle.

sel · Accepted Answer · 2012-09-12 08:46:54Z

1

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

answered Sep 12, 2012 at 8:46

sel

4,9421 gold badge18 silver badges23 bronze badges

Comments

Ketan Bhavsar · Accepted Answer · 2012-09-12 08:41:00Z

0

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;

answered Sep 12, 2012 at 8:41

Ketan Bhavsar

5,42610 gold badges40 silver badges70 bronze badges

1 Comment

Maksym Polshcha Over a year ago

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON r.id = rr.id'

Himanshu · Accepted Answer · 2012-09-12 09:17:20Z

0

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

edited Sep 12, 2012 at 9:17

Himanshu

32.8k32 gold badges117 silver badges137 bronze badges

answered Sep 12, 2012 at 8:47

Dipul Patel

1876 bronze badges

1 Comment

Maksym Polshcha Over a year ago

Syntax error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'unique(r.link) from resultitem r )' at line 1

Collectives™ on Stack Overflow

MySQL: delete rows returned by subquery

4 Answers 4

2 Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related