[thelist] MySQL - Remove "almost" duplicates

Stephen Rider evolt_org at striderweb.com
Sat Aug 2 09:28:05 CDT 2008

Hi all --

I'm trying to remove records from a table that are identical except  
for the "time" field.  I found the following on the Net...

To delete similar records, i.e. where the records are not the same but  
one field is the same and only one copy needs to be preserved, try the  
following SQL:

	    delete from T1
	    from MyTable T1, MyTable T2
	    where T1.dupField = T2.dupField
	    and T1.uniqueField > T2.uniqueField

This will delete all records from the table MyTable which have the  
same value for the field dupField, leaving that record which has the  
lowest value in uniqueField.

The table has more fields than that, so my version is more like:

	    FROM MyTable T1, MyTable T2
	    WHERE T1.dupField = T2.dupField
	    AND T1.dupField2 = T2.dupField2
	    AND T1.dupField3 = T2.dupField3
	    AND T1.uniqueField > T2.uniqueField

...but it doesn't work.  I think the problem is in the second line.

Any ideas how to make this work for MySQL?

Thanks in Advance

Stephen Rider

More information about the thelist mailing list