[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:
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/>
>
More information about the thelist
mailing list