Re: removing duplicate entries from a table

Mark,

About how many rows are we talking about? Just curious...

Log Miner might be a viable option. It comes with 8i but if the database in question is v8.0.x then you can still read them from an 8i database. From Log Miner you can extract the UNDO_SQL for the rows you want to delete.

If you are running Oracle7, then Log Miner is not an option at all and all you've got left is:

    select key-col1, key-col2, ..., count(*) dups, 'delete table-name where rowid = '''||max(rowid)||''';' cmd

    from table-name
    group by key-col1, key-col2, ...
    having count(*) > 1;

If you only have duplicates (i.e. two copies) you can decide if you want to run the 'cmd'. After testing things out in SQL*Plus, you can just spool out the DELETE commands by entering the following SQL*Plus commands first...

    col key-col1 noprint
    col key-col2 noprint
    ...
    col cnt noprint

which will just spool out the generated DELETE commands...

Be careful! Hope this helps...

-Tim

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, January 13, 2003 10:35 AM

> Hi List
>
> Is there a tool for removing duplicate entries from a table - except that
> the key field is not the same. I ran some data imports more than once and
> was hoping someone could point me in the direction of a way to undo my
> mistake.
>
> Any help will be most appreciated
>
> Thanks
> Mark Warner
>
> ______________________________________________
> "The information contained in this communication is confidential and
> may be legally privileged. It is intended solely for the use of the
> individual or entity to whom it is addressed and others authorised to
> receive it. If you are not the intended recipient you are hereby
> notified that any disclosure, copying, distribution or taking action
> in reliance of the contents of this information is strictly prohibited
> and may be unlawful. Absa is liable neither for the proper, complete
> transmission of the information contained in this communication, nor
> for any delay in its receipt, nor for the assurance that it is
> virus-free."
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mark Warner
> INET: margarete_at_absa.co.za
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).