0

I'm struggling with what I thought would be simple. I need to delete some duplicate rows that got created in a table following the release of some buggy code.

The query I've tried is as follows:

delete from buzz where id IN (select distinct b1.id from buzz b1, buzz b2 where b1.id != b2.id and b1.message = b2.message);

The select definitely selects the correct ids that I want to delete, however I get the error

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

I've tried every different thing that I can think of, so now I'm thinking I'm just approaching this the wrong way.

Can someone help?

mark
  • 1,591
  • 16
  • 36

3 Answers3

0

There is a little trick to work-around the issue in MySQL. It works on MySQL 5.6 at list, just wrap the sub-select with another one sub-select:

delete from buzz where id IN (select * from 
(select distinct b1.id from buzz b1, buzz b2 where b1.id != b2.id 
and b1.message = b2.message) Tab_alias);

Also you can refer to This.

Community
  • 1
  • 1
Ispirer SQLWays Migrations
  • 900
  • 7
  • 16
0

Try this:

delete from buzz where id IN (
  select id  from (
      select distinct b1.id
      from buzz b1, buzz b2 
      where b1.id != b2.id 
      and b1.message = b2.message) 
   as tempTable);
SMA
  • 33,915
  • 6
  • 43
  • 65
0

You really don't need a subquery for this. This should work:

DELETE b1
FROM buzz b1,
     buzz b2
WHERE b1.id > b2.id
  AND b1.message = b2.message;

This deletes the duplicates but leaves the original in the table.

kums
  • 2,547
  • 2
  • 11
  • 16
  • All of these solutions were great, but this was perfect for our needs since it was useful to leave the original in place. – mark Oct 27 '14 at 15:13
  • although, I should add you need to use "distinct b1" or you get lots of duplicate ids. – mark Oct 27 '14 at 15:49