Delete duplicate entries except for one entry

Question

I have a table with several duplicate entries:

ID Title ----------------- 1 Article A 2 Article A 3 Article B 4 Article C 5 Article A 

In the above case, I need all the duplicate headers and leave only one.

Article B and Article C are excellent. I need to delete article A except one.

Output Example:

 ID Title ----------------- 1 Article A 3 Article B 4 Article C 

Note. I am not interested in which identifier to save or delete. All I want is one entry that will be relevant.

Assuming that I have a huge mass of entries with duplicate names

Any suggestion?

Answer 1

 DELETE t1 FROM tTable t1, tTable t2 WHERE t1.fieldName = t2.fieldName AND t1.id > t2.id 

ELSE

create another table below

 CREATE TABLE myTable_new (ID INT PRIMARY KEY, Title varchar(20)) 

and add the values ​​as

 INSERT INTO myTable_new (ID, Title) SELECT ID, DISTINCT Title FROM old_table 

Considering old_table is an earlier table ...

Answer 2

This will always leave the line with the lowest ID -

 DELETE t2 FROM `table` t1 INNER JOIN `table` t2 ON t1.Title = t2.Title AND t1.ID < t2.ID 

Answer 3

 DELETE FROM table WHERE Title = 'Article A' AND ID <> 1 

Just select the identifier to save.

Answer 4

Add a UNIQUE to the Title column, ignoring repeated key errors:

 ALTER IGNORE TABLE my_table ADD UNIQUE my_index (Title) 

Of course, such a restriction will also prevent the addition of additional duplicates to the table in the future; if you do not want to enforce such a restriction in the future, you can simply abandon the restriction after creating it:

 ALTER TABLE my_table DROP INDEX my_index