ERROR 1093 (HY000)
Let's reproduce the error by our case. In which, we were trying to delete duplicate rows in a table fruits.
First of all, let's see the content of the table fruits.
mysql> select id, name from fruits;
+----+------------+
| id | name |
+----+------------+
| 1 | banana |
| 2 | cherry |
| 3 | mango |
| 4 | watermelon |
| 5 | kiwi |
| 6 | pineapple |
| 7 | orange |
| 8 | guava |
| 9 | banana |
| 10 | apple |
| 14 | banana |
| 15 | peach |
| 16 | mango |
| 17 | grapes |
| 18 | strawberry |
| 19 | guava |
| 20 | banana |
| 21 | apple |
+----+------------+
18 rows in set (0.00 sec)
As you can see, we have duplicate fruits in the table, such as banana, mango, guava, and so on. let's list the duplicated id of them by grouping.
mysql> select id from fruits group by name having count(name) > 1 order by 1;
+----+
| id |
+----+
| 1 |
| 3 |
| 8 |
| 10 |
+----+
4 rows in set (0.00 sec)
Now, we try to delete them with a subquery on the same table.
mysql> delete from fruits where id in (select id from fruits group by name having count(name) > 1 order by 1);
ERROR 1093 (HY000): You can't specify target table 'fruits' for update in FROM clause
The error 1093 means that we use the same table in the subquery with the delete's. MySQL doesn't allow this manner of DML.
Solution
My solution is to create a copy of the original table for temporary usage. Then, we will have two different tables with the same content, we use both of them in the above DML.
Copy Table
Clone a temporary table named fruits_copy like fruits
This step includes creating a table and copy all the content.
mysql> create temporary table fruits_copy like fruits;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into fruits_copy select * from fruits;
Query OK, 18 rows affected (0.01 sec)
Records: 18 Duplicates: 0 Warnings: 0
Delete Rows
Delete from the table by subquerying the clone table.
mysql> delete from fruits where id in (select id from fruits_copy group by name
having count(name) > 1 order by 1);
Query OK, 4 rows affected (0.01 sec)
Let's list the result. You can see there're 4 rows removed.
mysql> select id, name from fruits;
+----+------------+
| id | name |
+----+------------+
| 2 | cherry |
| 4 | watermelon |
| 5 | kiwi |
| 6 | pineapple |
| 7 | orange |
| 9 | banana |
| 14 | banana |
| 15 | peach |
| 16 | mango |
| 17 | grapes |
| 18 | strawberry |
| 19 | guava |
| 20 | banana |
| 21 | apple |
+----+------------+
14 rows in set (0.00 sec)
This is how we solve the error. But there're still some duplicates left, you can just re-execute the delete statement again and again.
One more thing, you don't have to remove temporary table fruits_copy, because MySQL will drop it automatically when the session is ended.