[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...

***quote***
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.
***quote***

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

	    DELETE FROM T1
	    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

-- 
Stephen Rider
<http://striderweb.com/>






More information about the thelist mailing list