[thelist] MySQL - Remove "almost" duplicates

Kurt Milam kurt.milam at gmail.com
Sat Aug 2 15:09:55 CDT 2008

Hi Steve,

That syntax you have is definitely not correct.

I searched around a little and found this:

Replace a,b with your list of column names that define duplicate values.

ALTER IGNORE TABLE [TableName] ADD UNIQUE INDEX [IndexName] ([DupeColumn,

Afterwards, you may wish to drop the index (or you may not - it's up to
ALTER TABLE [TableName] DROP INDEX [IndexName]

Hope that does the trick.


On Sat, Aug 2, 2008 at 7:00 PM, <thelist-request at lists.evolt.org> wrote:

> Date: Sat, 2 Aug 2008 09:28:05 -0500
> From: Stephen Rider <evolt_org at striderweb.com>
> Subject: [thelist] MySQL - Remove "almost" duplicates
> To: thelist at lists.evolt.org
> 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