Re: Duplicated records

Hi.
Thanks for the article...
But, I have read it and the query works very slow...
My table have aprox. 180.000 records (correct) and in entire table it has
aprox.360.000 records(duplicated)...
I tried to execute a query to delete the duplicated records, but it
worked very
very slow... look:

# select * from lanctos order by numos;
numos | field1 | field2 | field3 |...
00001 | test | T2-2 | 2 |...
00001 | test | T2-2 | 2 |...
00002 | Blabla | 0 | ABC |...
00002 | Blabla | 0 | ABC |...
00003 | Llllll | Oooooo | Rrrrrr |...
00003 | Llllll | Oooooo | Rrrrrr |...
...

The records is entire duplicated (with all fields having the same data),
thinking the "numos" fields as primary key I have executed the query:

# DELETE from lanctos where not oid=(select oid from lanctos as l2 where
l2.numos=lanctos.numos limit 1);

I have tested others querys with EXPLAIN command to examine the performance
time, and this query was the best performance I got... but its is slow. Other
query is:

# DELETE from lanctos where not exists (select '1' from lanctos as l2 where
l2.numos=lanctos.numos and not l2.oid=lanctos.oid);

Is there a way to delete those duplicated records faster??? Remembering the
table have aprox 360.000 records...
Is better I create other table and copy those data??? How should I created???

Thanks.

Quoting Andreas Kretschmer <akretschmer(at)despammed(dot)com>:
> am 24.05.2005, um 17:59:31 -0300 mailte lucas(at)presserv(dot)org folgendes:
>> Hi.
>> How can I delete the duplicated records with "DELETE FROM TABLE WHERE..."
>> clause??
>
> Please read http://www.gtsm.com/oscon2003/deletetid.html
>
> Its a very good article about this problem.
>