[thelist] MySQL - Remove "almost" duplicates
Jeremy Weiss
eccentric.one at gmail.com
Sat Aug 2 15:05:32 CDT 2008
I haven't a clue if this is right, but if it were me, I'd try something like this:
DELETE FROM T1 WHERE
(SELECT * 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)
**use at your own risk. may delete every record in the table,
burn your toast, and wreck your car. I assume no responsibility**
-jeremy
-----Original Message-----
From: thelist-bounces at lists.evolt.org [mailto:thelist-bounces at lists.evolt.org] On Behalf Of Stephen Rider
Sent: Saturday, August 02, 2008 9:28 AM
To: thelist at lists.evolt.org
Subject: [thelist] MySQL - Remove "almost" duplicates
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/>
--
* * Please support the community that supports you. * *
http://evolt.org/help_support_evolt/
For unsubscribe and other options, including the Tip Harvester
and archives of thelist go to: http://lists.evolt.org
Workers of the Web, evolt !
More information about the thelist
mailing list