Re: Help: Delete all rows except 1 of a set of rows.

Thomas Kyte wrote in message <360afe55.95405355_at_192.86.155.100>... <Snip>
>>In a nutshell, there should of been a unique key assigned to a table, but
>>there wasn't. I need to clean up the table and create a new unique key
when
>>done.
>>
>>I can find all of the rows that have duplicates using:
>>
>>SQL> select risn, crit_code, crit_rev from ri_result group by risn,
>>crit_code, crit_rev having count(*) > 1;
>>
>>I just don't know how to do the delete, leaving 1 row of each.
>>
>>Thanks for any help,
>> Rich W.
>>
>
>if you want to just delete dups, leaving a 'random' one of them behind, the
>following delete will do it:
>
>delete from ri_result
>where rowid <> ( select max(rowid)
> from ri_result b
> where b.risn = ri_result.risn
> and b.crit_code = ri_result.crit_code
> and b.crit_rev = ri_result.crit_rev )
>/

<Snip>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Government
>Herndon VA

This was exactly what I was looking for, it worked fine. Thanks alot for the help.

• Rich W.