Re: How to delete duplicate rows

From: Jomarlen <jomarlen_at_aol.com>
Date: 1997/12/23
Message-ID: <19971223015200.UAA12059@ladder01.news.aol.com>#1/1

>Does anyone have an SQL script for deleting duplicate rows in a table.

Sorry I didn't see this earlier, I've been on vacation. Using ROWID makes this very simple.

Sample table :

SQL> desc test

 Name                            Null?    Type
 ------------------------------- -------- ----
 TEST_VALUE                               NUMBER

To delete duplicates

SQL> delete from test a
  2 where exists
  3 (select 1 from test b
  4 where b.test_value = a.test_value
  5 and b.rowid > a.rowid)

This deletes all but the first occurence of duplicate records, unlike some of the
other ideas posted, which delete them all.

John Received on Tue Dec 23 1997 - 00:00:00 CST