Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Finding duplicate rows in a table
Billy Verreynne wrote:
>
> chris <x_at_worldnet.att.net> wrote in article
> <34142F08.768F_at_worldnet.att.net>...
> > 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).
> >
>
> SELECT
> primary_key, count(*)
> FROM foo
> GROUP BY primary_key
> HAVING count(*) > 1
>
> regards,
> Billy
If primary_key is truly a primary key, the above query will return "NO ROWS FOUND". To keep you table copy from blowing off you will will need to check the destination table for duplicates. If you are doing the copy with a LINK then the INSERT can be qualified with a subquery:
INSERT INTO MYTABLE
SELECT * FROM MYTABLE_at_linkname REMOTE
WHERE NOT EXISTS (SELECT 'X' FROM MYTABLE LOCAL
WHERE LOCAL.primary_key = REMOTE.primary_key);
This will not try to copy a duplicated key. Received on Tue Sep 09 1997 - 00:00:00 CDT