Re: Deleting Duplicates

In article <1992Jul23.091611.1_at_jaguar.uofs.edu> littlec1_at_jaguar.uofs.edu (Chris Little) writes:
>In article <VN70NB1w165w_at_iowegia.uucp>, bob_at_iowegia.uucp (Bob Powers) writes:
>> How can I delete duplicate rows in a table using SQL*PLUS. This is
>> an unindexed table and I would like to keep it that way.
>
>I'm relatively new at the job, but I suspect you could create a similar
>table, do an INSERT SELECT UNIQUE from the old table to the new table,
>delete all rows from the old table, copy all rows from the new table
>to the old, and then drop the new one. Maybe there's a better way.
>

Try customizing the following...

delete from TABLE_NAME a
 where a.rowid < (select max(b.rowid)

                   from TABLE_NAME b
                   where b.field1 = a.field1
                   and   b.field2 = a.field2);

Mike   Received on Thu Jul 23 1992 - 20:09:19 CEST