Re: Finding duplicate rows in a table

From: AMARENDRA B NETTEM <nettama_at_charlie.cns.iit.edu>
Date: 1997/09/14
Message-ID: <341BEA7F.2BAE@charlie.cns.iit.edu>#1/1

chris 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

For example say you have a table name 'test' with columns x and y. primary key is (x,y).

TO FIND THE DUPLICATE ROWS

---

 SELECT * from test a
 where a.rowid <> (select max(b.rowid) from test b

                where a.x = b.x
                   and a.y = b.y);

TO DELETE DUPLICATE ROWS

---

delete from test a
where a.rowid <> (select max(b.rowid) from test b

               where a.x = b.x
                  and a.y = b.y);

Hope this helps
AMARENDRA B NETTEM

---

    AMARENDRA B NETTEM
    Certified Oracle DBA
    WHITTMAN-HART INC., CHICAGO
    Homepage : http://www.iit.edu/~nettama     E-mail : anettem_at_whittman-hart.com

             nettama_at_charlie.cns.iit.edu Received on Sun Sep 14 1997 - 00:00:00 CDT