Re: Finding duplicate rows in a table

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..."