Re: How do I locate duplicate rows

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK>
Date: Thu, 30 Nov 2000 21:54:26 GMT
Message-ID: <3a26cb9f.853998625@news.alt.net>

On Wed, 29 Nov 2000 16:29:18 -0900, Anthony Valentine <amv_at_sbsalaska.com> wrote:

>Hello All!
>
>I am trying to add a unique constraint to a table, but I get the
>following error:
>
>ORA-02299: cannot validate (ORACLE.LOS160MASTER_UK) - duplicate keys
>found
>
>Is there any way to get Oracle to tell me which rows contain the
>duplicate keys?
>
>
>Thanks in advance!
>
>Anthony Valentine
>
>

Assuming Id is the primary key, and Name is the duplicate column.

SELECT
        A.Id
FROM

	TableName A,
	TableName B
WHERE
	A.Name = B.Name

    AND NOT A.Id = B.Id;

If you have no primary key, you can use ROWID.

Brian Received on Thu Nov 30 2000 - 15:54:26 CST