| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help: Delete all rows except 1 of a set of rows.
A copy of this was sent to "Rich W." <rwerning_at_NOSPAM.execpc.com>
(if that email address didn't require changing)
On Wed, 16 Sep 1998 11:58:16 -0500, you wrote:
>Using Oracle 7.3.3.2, updating via SQL*Plus.
>
>
>What I need to do is select from a table all non unique records with certain
>criteria. If there are rows that match, delete all of the rows except one
>(it doesn't matter which one it is).
>
>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 )
/
best results speed wise if you have an index on (risn,crit_code,crit_rev) prior to executing else partial full table scans will occurr for each row in the ri_result table.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Sep 16 1998 - 13:09:21 CDT
 |
 |