[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