Re: delete 2 of the 3 triplicates from a table

From: De DBA <dedba_at_tpg.com.au>
Date: Sun, 09 Feb 2014 11:42:50 +1000
Message-ID: <52F6DD1A.4020402_at_tpg.com.au>

Many versions ago, I've used a primary key to accomplish this. Assuming that the ID is indeed unique, and the information in any other columns is exchangeable, you can define a primary/unique key on the ID column and enable validate that with "EXCEPTIONS INTO {TABLE}" clause. Then use the rowids in the exceptions table to delete all but one of the duplicates.

Cheers,
Tony

On 08/02/14 02:42, Ruel, Chris wrote:
>
> There are number of ways you can accomplish this.
>
> 1.Depending on the size of the table, create a new table as select using group to identify the “unique” rows. Truncate old table, insert back in from copy.
>
> 2.Join table to itself, select the MIN rowid for each column pair and use that rowid to delete. You have to be careful with this though because you have to take into consideration if there are rows with non-duplicates. You should be able to design the delete to consider this.
>
> 3.It has been awhile since I did this myself but I think there is a way to use RANK/PARTITION BY to identify and delete rows with RANK > 1
>
> Of course, take a backup of table before trying anything. Perhaps copy the table first to test out your methods.
>
> Chris..
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Feb 09 2014 - 02:42:50 CET