Re: Finding duplicate rows in a table

Gary England 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).
 

> >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);

Alternatively, you can use a set operation -- which is liable to be a great deal faster for large tables:

insert into mytable
select * from mytable_at_linkname
MINUS
select * from mytable; Received on Tue Sep 09 1997 - 00:00:00 CDT