Re: removing duplicates rows

<<From: "al" <asalvado_at_ev1.net>
Date: Thu, 24 June 1999 08:39 PM EDT
[Quoted] Message-id: <gjAc3.5747$d5.725031_at_news21b.ispnews.com>

[Quoted] If a tables rows are accidently duplicated, how do you remove duplicates?

1st you can count the rows, and then use this count in the following SQL: DELETE: FROM table
WHERE rownum < ( (row_count/2) + 1);

This works only if you duplicated the rows all at once. What's the proper way of doing it?

Thanks.
>>

You have not stated the table size or the number of dups. The DELETE statement [Quoted] after a fashion would work, but may create some serious performance problems. [Quoted] The most direct way would be something like:

create table new_tbl_nm
.... storage parameters ....
unrecoverable parallel(degree 3)
as
select /*+ parallel(a,3) */
 distinct
 *
from old_tbl_nm a
/
rename old_tbl_nm to old_tbl_nm_backup
/
rename new_tbl_nm to old_tbl_nm
/

After you are sure that everything is OK, then and only then:

DROP TABLE Cecil J. Jones
Chief Technologist
Information Technology Paradigms, Incorporated Data Warehouse, Data Marts and
Decision Enabling Technology Consultants (415) 454-9424 -- Fax (415) 454-9489
E-Mail: cjjitp_at_aol.com Received on Fri Jun 25 1999 - 06:41:44 CEST