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@despammed.com>:
> am  24.05.2005, um 17:59:31 -0300 mailte lucas@presserv.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.
>