Re: Please help write a query **Urgent** tks

Yes, Oracle has a provision for finding duplicate keys; it begins with the following table:

CREATE TABLE exception
 (

  row_id                     ROWID,
  owner                      VARCHAR2(30),
  table_name                 VARCHAR2(30),
  constraint                 VARCHAR2(30)

The next step is to add a unique constraint to the table in question:

ALTER TABLE <TABLE_NAME>
 ADD CONSTRAINT <TABLE_NAME>UNIQ
 UNIQUE
 ( duplicate_fileld(s)
 )
 EXCEPTIONS INTO EXCEPTION
/

Of course the constraint will not be enabled due to data errors, and all of the 'offending' ROWIDs will be written to the EXCEPTION table. You can then query the source table for the 'offending' rows.

David Fitzjarrell
Peter Shankey <shankeyp_at_its.charlestoncounty.org> wrote in message news:<3B69F008.73988B59_at_its.charlestoncounty.org>...
> Oracle comes with a method for doing this! I cannot remember the script which must be run but if
> you start looking in the sql referance manual under alter table add primary key you will find it .
>
> Pete's wrote:
>
> > Try something like the following:
> >
> > select column_name_with_duplicate_names, count(*)
> > from your_table_name
> > having count(*) > 1
> > group by column_name_with_duplicate_names;
> >
> > After you get the duplicate names, you then could select the rowid's
> > if need be, then delete whatever row you wish.
> >
> > select column_name_with_duplicate_names,rowid
> > from your_table_name
> > where column_name_with_duplicat_names in ('NAME1',.......);
> >
> > HTH,
> > EP
> >
> > "OCP" <mr_ocp_at_yahoo.com> wrote in message news:<F_r97.106$nF6.2620_at_newscontent-01.sprint.ca>...
> > > Hi
> > > We have a table the contains several duplicate records however each record
> > > is with an unique primary key, I need to write a query to see how many
> > > records are duplicate, there is a name field which has several duplicate
> > > names under separat unique id, so is there a way that I can find out how
> > > many such duplicate names exists in the table.
> > >
> > > Please give me some ideas - this is urgent - appreciate your help
> > > Mike
> > > mr_ocp_at_yahoo.com
Received on Tue Aug 07 2001 - 10:51:04 CDT