[thelist] MySQL - Remove "almost" duplicates

[Kurt Milam]

Hi Steve,

That syntax you have is definitely not correct.

I searched around a little and found this:
ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);

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

So:
ALTER IGNORE TABLE [TableName] ADD UNIQUE INDEX [IndexName] ([DupeColumn,
DupeColumnN]);

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

Hope that does the trick.

Kurt

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/>
>