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 <[EMAIL PROTECTED]>:
am 24.05.2005, um 17:59:31 -0300 mailte [EMAIL PROTECTED] 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.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
