|
|
Finding
Duplicate Values in a Column
Yes, key
constraints are supposed to prevent this, but suppose you have a bunch of
values already in a table column, and suddenly want to put a primary key
or unique constraint on those values...
If you have duplicate values, Oracle will complain, but will not tell you
where/what these values are...
To locate these values, you can run the following query:
select col1 , rowid
from my_table a
where a.rowid not in (select max(rowid) from my_table b where b.col1=a.col1)
Replace
my_table with your table name, and col1 with your column name.
Basically, this query checks each value, and compares the rowid of that value...if
rowids are different, as they will be with dups, it will report them.
|
|
|
