Re: delete 2 of the 3 triplicates from a table
- From: De DBA <dedba@xxxxxxxxxx>
- To: Chris.Ruel@xxxxxxx
- Date: Sun, 09 Feb 2014 11:42:50 +1000
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..
Other related posts:
