Re: Finding duplicate rows in a table

chris (x_at_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:                      <->
<->                                                               <->
<->             http://homepage.interaccess.com/~akaplan          <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->