Subject: Re: Finding duplicate rows in a table
Newsgroups: comp.databases.oracle.server
References: <34142F08.768F@worldnet.att.net>
Organization: InterAccess, Co. - Chicagoland's Full Service Internet Provider
Reply-To: akaplan@interaccess.com
Distribution: 

chris (x@worldnet.att.net) wrote:
: I don't know how, but when I try and copy one of my tables to another
: server across the net, I get a constraint violation on the primary key.
: How can I find the rows in the original table that have duplicate
: primary key information (the primay key consists of two columns).
: 
: chris
You are most likely getting the primary key constraint violation because:
1) You already have data in the table you are inserting into
2) You do not have a primary key in the remote table, and there are
duplicate values in it.

To see the duplicate records, follow the example below:

You are inserting records from TABLE B into TABLE A, and the primary key
on both tables is COLUMN A and COLUMN B. Issue the following query to
find thte duplicate records:

SELECT * FROM TABLE_B
WHERE (COLUMN_A||COLUMN_B) IN
      (SELECT COLUMN_A||COLUMN_B
       FROM TABLE_A);

To find if there are any duplicate values in TABLE_B alone (if there is a
primary key then there will be no duplicates) type the following:

SELECT COUNT(*), COLUMN_A, COLUMN_B
FROM TABLE_B
GROUP BY COLUMN_A, COLUMN_B
HAVING COUNT(*)>1;

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 105+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page dex.htm">Back to Ari Kaplan's Home Page