query optimisation [message #3037] |
Sun, 01 September 2002 21:08 |
Deepa
Messages: 269 Registered: November 2000
|
Senior Member |
|
|
hi,
i want to delete the duplicate records .but one copy of the duplicate record should be present in the table.
i am using the following query.
delete from test a where a.rowid not in (select max(b.rowid) from test b where b.rowid=a.rowid).the above query is taking more time if i have some 60000 records.
i want the query to be optimised to do that function.
thanks,
deepa
|
|
|
Re: query optimisation [message #3050 is a reply to message #3037] |
Mon, 02 September 2002 06:38 |
Mark
Messages: 284 Registered: July 1998
|
Senior Member |
|
|
I don't imagine that this is any quicker, but feel free to give it a try ...
delete from TABLE
where rowid in (
select min(rowid)
from TABLE T2
having count(UNIQUE)>1
group by UNIQUE
);
|
|
|