Re: Finding duplicate rows in a table

In article <774143395snz_at_aboy.demon.co.uk> MarkB_at_aboy.demon.co.uk writes:
>In article <CsuKyI.8qy_at_bigtop.dr.att.com>
> jameson_at_coupler.drink-eid "131W10000-JamesonJ(DR2483" writes:
>
>> There are several tables in our database that contain duplicate records. Isn't
>> there
>> some simple SQL code to locate duplicate rows in a table? I would really
>> appreciate
>> it if you could post a sample.
>>
>> Thanks,
>>
>> Jeremy Jameson
>>
>
>select #your primary key here#, count (#your primary key here#)
>from table
>group by #your primary key here#
>having count (#your primary key here#) > 1
>/
>
>The question is, are these rows truly identical throughout, or do they
>simply have identical primary keys?
>
>If the former, it doesn't matter which you delete, therefore simply
>delete the one with the max(rowid) in a correlated query.
>
>If the latter, you'll have to decide which you want to keep and
>delete explicitly by rowid.
>
>Stick a unique index on the key is my suggestion.
>
>M
>--
>-----------------------------------------------------------------------------
> MarkB_at_aboy.demon.co.uk
>Lyric Quiz of the Week: "One and one and one is three..."

Here's the way to eliminate the duplicate rows with a correlated subquery:

Assume first that col-name is the name of a candidate primary key column, eg. something that you can use to retain each unique row in your table (employee number, badge number, etc.)

delete from table-name outer
  where rowid >
    (select min(rowid) from table-name
      where col-name = outer.col-name);

Enjoy!

Ed Dansker
Oracle Corporation Received on Thu Jul 28 1994 - 14:29:50 CEST