MySQL Delete duplicate data

Title: MySQL Database delete duplicate data

Because it is MySQL, other data crying commands are not available in MySQL. Don't assume that SQL scripts are used

delete  from  Table1where  field1 in  ( select  field1 from  table1 group  by  field1 having  count  (field1) > 1 ) and  rowid not  in  (select  min  (ROWID) from  table1 group   by  field1 having  count  (field1) >< Span class= "Hljs-number" >1 ) 

This is the command for the Oracle database. MySQL will use the error.

You can ' t specify the target table ' table1 ' for update with FROM clause error.

Because there is no rowid in MySQL and MySQL rules can not be selected in the same table and update, delete and so on.

So the question is, how do you get rid of duplicate data?

Now there are two cases of repetition:

1, the whole repetition: for example, there is no primary key, may appear, the line and the line is exactly the same.
This can be achieved with auxiliary temporary tables.
You can use SQL statements:

 CREATE TABLE temp SELECT DISTINCT * FROM table1 ;

Then delete the original table by manually modifying the table name ...
You can also use SQL scripts directly:

CREATE TABLE temp SELECT DISTINCT * FROM table1 ;DELETE FROM table1 ;INSERT INTO table1  SELECT * FROM temp;DROP TABLE temp;

2, only a few key fields of the value is repeated, may be inserted or updated, the operation is repeated caused.
I told you before. mysql cannot insert and update a table at the same time, so we need to change the path.
My approach is to use temporary tables for indirect operations.

    CREATE TABLE tmp AS SELECT MIN(ID) AS col FROM test GROUP BY RID;    DELETE FROM test WHERE ID NOT IN (SELECT col FROM tmp);     DROP TABLE tmp;

PS: Here the RID is a repeating field (one). A row of data with the lowest ID is reserved. Delete the other duplicate rows.

There may be a way, this is just the method I find now, if there is any new method, you can add Oh.

MySQL Delete duplicate data