Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Finding duplicate rows in a table

Re: Finding duplicate rows in a table

From: Gary England <gengland_at_hiwaay.net>
Date: 1997/09/09
Message-ID: <34152F51.2407@hiwaay.net>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US