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