Re: Query to find non-unique records

"Michael Bierenfeld" <michael.bierenfeld_at_atmiralis.de> wrote in message news:3AEFC07E.DABA5C24_at_atmiralis.de...
> ewong wrote:
> >
> > I have a table:
> > id integer,
> > tag1 varchar2(5),
> > tag2 varchar2(5),
> > text clob
> > ...
> >
> > The primary key is (id,tag1,tag2). I disabled the pk couple days ago
> > and find that I am not able to enable it anymore because some new
> > records don't have unique pks on the three columns. How can I do query
> > to track down those non-unique records?
> >
> > Thanks.
>
> select a.id, a.tag1, a.tag2 from table a, table b
> where a.id = b.id and a.tag1 = b.tag1 and a.tag2 = a.tag2 and a.rownum
> <> b.rownum;
>
> :-) could be used in the where clause of a delete statement as well.
>
> Regards
>
> Michael

Make rownum rowid or it will definitely not work.

Regards,

Sybrand Bakker, Oracle DBA Received on Wed May 02 2001 - 04:04:10 CDT