This is a short tutorial how to remove duplicated entries from MySQL DB Table
First idea was just to do a simple DELETE statement on a SubSelect
DELETE FROM user WHERE id IN (SELECT u2.id
FROM user AS u1
JOIN user AS u2 ON u1.name=u2.name
WHERE u1.id < u2.id)
Unfortunatelly this gives back an error message.
#1093 - You can't specify target table 'ex_user' for update in FROM clause
MySQL Documentation says about that
Error 1093 (ER_UPDATE_TABLE_USED)
SQLSTATE = HY000
Message = "You can't specify target table 'x'
for update in FROM clause"
This error occurs in cases such as the following:
UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
You can use a subquery for assignment within an UPDATE statement because subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table (in this case, table t1) for both the subquery's FROM clause and the update target.
I was searching for a solution without using temporary table but didn´t find one yet.
CREATE TEMPORARY TABLE duplicated_temp(id INT) TYPE=HEAP;
INSERT INTO duplicated_temp SELECT u2.id FROM ex_user AS u1 JOIN user AS u2 ON u1.name=u2.name WHERE u1.id < u2.id;
DELETE FROM user WHERE id IN (SELECT id FROM duplicated_temp WHERE 1);
DROP TABLE duplicated_temp;
Well thats it.