Re: Find out duplicate keys

Vito Kwan wrote:
>
> Hi all,
> I wonder if there is a easier way to delete the rows with duplicate
> keys (including single PK and composite PKs) without using resursion or
> alias of the table.
> Thanks for your help.
>
> Vito Kwan
> vkwan_at_dorsai.org
>
> --
> Vito Kwan
> vkwan_at_dorsai.org
> ======================================================================
> I am Sir Oracle, And when I ope my lips, let no dog bark!
> Shakespeare: The Merchant of Venice

We use the following as an easy way to find duplicates in a table:

assume job_nbr is primary key

select job_nbr
from job_table
group by job_nbr
having count(*) > 1;

assume job_nbr, tag_nbr are primary key:

select job_nbr, tag_nbr
from job_table
group by job_nbr, tag_nbr
having count(*) > 1;

You can use these to find the keys of the rows you want to delete then delete them, or incorporate them as subqueries in your delete statement.     

Good luck! Received on Mon Apr 08 1996 - 00:00:00 CEST