If you have a table in MySQL with duplicate records, you can easily get a list without the duplicates by using the DISTINCT keyword in the SELECT-statement:
SELECT DISTINCT * FROM table
If you want to delete the duplicate records, you can create a new table without the duplicates, by combining the CREATE TABLE statement with SELECT DISTINCT:
CREATE TABLE newtable SELECT DISTINCT * FROM table
However, if you have similar records, where some, but not all fields are the same, the DISTINCT approach does not work. This is an excerpt of a table with songs:
[TABLE=2]
I’d like to delete the similar records, keeping just one entry for each artist/title combination. The SELECT DISTINCT statement will not work, as none of the records are exact duplicates. In other words,
SELECT DISTINCT * FROM table
will return the same table.
I could do a
SELECT DISTINCT title,artist FROM table
which results in
[TABLE=3]
but I want to keep the information in the other columns.
The solution in MySQL is to use the multiple-table syntax for the DELETE statement using the same table twice:
DELETE T1 FROM table T1, table T2 WHERE T1.duplicateField = T2.duplicateField AND M1.uniqueField > M2.uniqueField
For the example above, I could use
DELETE M1 FROM music M1, music M2 WHERE M1.title = M2.title AND M1.artist = M2.artist AND M1.cd*100+M1.track > M2.cd*100+M2.track
which results in
[TABLE=4]
do you know how to delete smilar records like that:
crazy
mrazy
crazy angels
drazy
ball
football
–>
mrazy
crazy angels
drazy
football
do you have any idea?