[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