Re: How to clean up duplicate records

If you want to look at what you are deleting first, run this query to identify the dup pkā€™s:

SELECT pk_colname FROM tabname
GROUP BY pk_colname HAVING COUNT(*) > 1;

It's a little harder to write a single SQL statement to automatically delete all but one -- and whenever I've had to do this I like to look at which ones I'm going to delete first. Then it depends how many I have to delete -- after selecting those that are going to be deleted if there are few I just hard code them:

DELETE FROM tabname WHERE pk_column IN ( pk1, pk2, pk3);

If there are many (more than a hundred or so) I feed the selected keys into a table and replace the pk1, pk2, pk3 with a SELECT pk_column FROM my_new_temp_table.

Tom

ewong wrote:

> I have a table (f1 integer, f2 integer, f3 varchar2) with an original
> composite primary key on (f1, f2). The pk was mistakenly disabled for
> couple days and I am not able to enable it now because there are couple
> thousands of duplicate identifical records inserted:
> f1 f2 f3
> 1 2 text1
> 1 2 text1
> 2 5 text2
> 2 5 text2
> ...
> Is there any kind of sql that can clean it up so that I can enable the
> pk again? I need to delete one of the two duplicate records.
>
> Any help is appreciated.
Received on Fri May 18 2001 - 00:46:05 CDT