FAQ
Hi gurus,
I need detect and delete duplicate rows in any table, somebody helpme
thanks!!!

@lex

Lic. Alexander Ordóñez Arroyo

Caja Costarricense del Seguro Social
Soporte Técnico - División de Informática
Telefono: 295-2004, San José, Costa Rica
Aordonez_at_ccss.sa.cr Icq# 30173325

------------------------------------------------------------

The true is out there in WWW

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alexander Ordonez
INET: aordonez_at_ccss.sa.cr

Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).

Search Discussions

  • Mark Leith at Feb 6, 2002 at 5:10 pm
    Alex,

    Here are a couple of scripts that have come from the list in the past:

    declare
    cursor get_dups is
    select pk_col1, pk_col2, pk_col3, count(*)
    from table
    group by pk_col1, pk_col2, pk_col3
    having count(*) > 1;
    dupRec get_dups%rowtype;
    begin
    for dupRec in get_dups loop
    delete from table
    where pk_col1 = dupRec.pk_col1
    and pk_col2 = dupRec.pk_col2
    and pk_col3 = dupRec.pk_col3
    and rownum = 1;
    end loop;
    end;
    /

    Identify duplicate records:

    select COL1,
    COL2,
    COL#,
    COUNT(*)

    from .
    group by COL1, COL2, COL#
    having count(*) > 1;

    Remove duplicate records:

    delete from . a
    where rowid < (

    select max(rowid)
    from . b
    where b.COL1 = a.COL1
    and b.COL2 = a.COL2
    and b.COL# = a.COL#
    );

    ===========================================

    Just for giggles, if you want to do this on DB2 as well, then check this
    out:

    http://www.searchDatabase.com/tip/1,289483,sid13_gci784575,00.html

    HTH

    Mark

    -----Original Message-----
    Ordonez
    Sent: 06 February 2002 15:33
    To: Multiple recipients of list ORACLE-L

    Hi gurus,
    I need detect and delete duplicate rows in any table, somebody helpme
    thanks!!!

    @lex

    Lic. Alexander Ordóñez Arroyo
    Caja Costarricense del Seguro Social
    Soporte Técnico - División de Informática
    Telefono: 295-2004, San José, Costa Rica
    Aordonez_at_ccss.sa.cr Icq# 30173325

    The true is out there in WWW

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Alexander Ordonez
    INET: aordonez_at_ccss.sa.cr

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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.com
    --
    Author: Mark Leith
    INET: mark_at_cool-tools.co.uk

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).
  • Viktor at Feb 6, 2002 at 5:25 pm
    Here is a query that was posted here a while ago:
    delete from pa_answer
    where rowid in (select rowid
    from p_answer pa
    where rowid > (select min(pa2.rowid)
    from p_answer pa2
    where pa.p_id = pa2.p_id and pa.p_name = pa2.p_name))

    This should help.

    Viktor

    Alexander Ordonez wrote:
    Hi gurus,
    I need detect and delete duplicate rows in any
    table, somebody helpme
    thanks!!!

    @lex
    Lic. Alexander Ordóñez Arroyo
    Caja Costarricense del Seguro Social

    Soporte Técnico - División de Informática

    Telefono: 295-2004, San José, Costa Rica

    Aordonez_at_ccss.sa.cr Icq# 30173325

    The true is out there in WWW

    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: Alexander Ordonez
    INET: aordonez_at_ccss.sa.cr

    Fat City Network Services -- (858) 538-5051 FAX:
    (858) 538-5051
    San Diego, California -- Public Internet
    access / Mailing Lists
    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).
    Do You Yahoo!?
    Send FREE Valentine eCards with Yahoo! Greetings!
    http://greetings.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Viktor
    INET: stant_98_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).
  • Babich , Sergey at Feb 6, 2002 at 5:36 pm
    Or to just count them:
    SELECT col1,col2.....coln,count(*) from table
    Group by col1,col2...coln
    Having count(*) >1;

    -----Original Message-----
    Sent: Wednesday, February 06, 2002 10:33 AM

    To: Multiple recipients of list ORACLE-L
    Subject: Duplicate rows

    Hi gurus,
    I need detect and delete duplicate rows in any table, somebody helpme
    thanks!!!

    @lex

    Lic. Alexander Ordóñez Arroyo

    Caja Costarricense del Seguro Social
    Soporte Técnico - División de Informática
    Telefono: 295-2004, San José, Costa Rica
    Aordonez_at_ccss.sa.cr Icq# 30173325

    ------------------------------------------------------------

    The true is out there in WWW

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Alexander Ordonez
    INET: aordonez_at_ccss.sa.cr

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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.com
    --
    Author: Babich, Sergey
    INET: SBabich_at_handexmail.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).
  • Rachel Carmichael at Feb 6, 2002 at 6:20 pm
    Courtesy of Ari Kaplan of this list:

    delete from &&table_name a where rowid > (select min(rowid)
    from &&table_name b
    where a.&&column_name=b.&&column_name
    )
    /

    this works if only one column is duplicated but you should be able to
    change it (concatenate all the columns together?) if you don't
    duplicate on just one column

    Alexander Ordonez wrote:
    Hi gurus,
    I need detect and delete duplicate rows in any table, somebody helpme

    thanks!!!

    @lex
    ------------------------------------------------------------
    Lic. Alexander Ordóñez Arroyo
    Caja Costarricense del Seguro Social
    Soporte Técnico - División de Informática
    Telefono: 295-2004, San José, Costa Rica
    Aordonez_at_ccss.sa.cr Icq# 30173325

    ------------------------------------------------------------
    The true is out there in WWW

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Alexander Ordonez
    INET: aordonez_at_ccss.sa.cr

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    --------------------------------------------------------------------
    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).
    Do You Yahoo!?
    Send FREE Valentine eCards with Yahoo! Greetings!
    http://greetings.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).
  • Stephane Faroult at Feb 6, 2002 at 6:26 pm

    Alexander Ordonez wrote:

    Hi gurus,
    I need detect and delete duplicate rows in any table, somebody helpme
    thanks!!!

    @lex
    ------------------------------------------------------------
    Lic. Alexander Ordóñez Arroyo
    Caja Costarricense del Seguro Social
    Soporte Técnico - División de Informática
    Telefono: 295-2004, San José, Costa Rica
    Aordonez_at_ccss.sa.cr Icq# 30173325

    ------------------------------------------------------------
    Check the EXCEPTIONS clause of the ALTER TABLE ... ADD CONSTRAINT
    command. Create an EXCEPTIONS table, create a unique constraint on the
    columns which *should* be unique, then proceed as follows :
    create table nodup
    as select distinct * from

    where rowid in (select row_id from exceptions

    where table_name = upper('');

    (I dislike distinct but sometimes ... :-))
    delete
    where rowid in (select row_id from exceptions

    where table_name = upper('');

    insert into
    select * from nodup;
    drop table nodup;
    truncate table exceptions;

    and go on with the next table. Write a script which takes a parameter
    for all this part, no need to retype it each time.

    --
    Regards,

    Stephane Faroult
    Oriole Ltd
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Stephane Faroult
    INET: sfaroult_at_oriole.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).
  • Alexander Ordonez at Feb 6, 2002 at 6:27 pm
    thanks for your help!!!

    @lex

    Lic. Alexander Ordóñez Arroyo

    Caja Costarricense del Seguro Social
    Soporte Técnico - División de Informática
    Telefono: 295-2004, San José, Costa Rica
    Aordonez_at_ccss.sa.cr Icq# 30173325

    ------------------------------------------------------------

    The true is out there in WWW
    -----Mensaje original-----
    De: Mark Leith [SMTP:mark_at_cool-tools.co.uk]
    Enviado el: Miércoles 6 de Febrero de 2002 10:48 AM
    Para: Multiple recipients of list ORACLE-L
    Asunto: RE: Duplicate rows

    Alex,

    Here are a couple of scripts that have come from the list in the past:

    ===========================================

    declare
    cursor get_dups is
    select pk_col1, pk_col2, pk_col3, count(*)
    from table
    group by pk_col1, pk_col2, pk_col3
    having count(*) > 1;
    dupRec get_dups%rowtype;
    begin
    for dupRec in get_dups loop
    delete from table
    where pk_col1 = dupRec.pk_col1
    and pk_col2 = dupRec.pk_col2
    and pk_col3 = dupRec.pk_col3
    and rownum = 1;
    end loop;
    end;
    /

    ===========================================

    Identify duplicate records:

    select COL1,
    COL2,
    COL#,
    COUNT(*)
    from .
    group by COL1, COL2, COL#
    having count(*) > 1;

    Remove duplicate records:

    delete from . a
    where rowid < (
    select max(rowid)
    from . b
    where b.COL1 = a.COL1
    and b.COL2 = a.COL2
    and b.COL# = a.COL#
    );

    ===========================================

    Just for giggles, if you want to do this on DB2 as well, then check this
    out:

    http://www.searchDatabase.com/tip/1,289483,sid13_gci784575,00.html

    HTH
    Mark

    -----Original Message-----
    Ordonez
    Sent: 06 February 2002 15:33
    To: Multiple recipients of list ORACLE-L


    Hi gurus,
    I need detect and delete duplicate rows in any table, somebody helpme
    thanks!!!

    @lex
    ------------------------------------------------------------
    Lic. Alexander Ordóñez Arroyo
    Caja Costarricense del Seguro Social
    Soporte Técnico - División de Informática
    Telefono: 295-2004, San José, Costa Rica
    Aordonez_at_ccss.sa.cr Icq# 30173325

    ------------------------------------------------------------
    The true is out there in WWW

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Alexander Ordonez
    INET: aordonez_at_ccss.sa.cr

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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.com
    --
    Author: Mark Leith
    INET: mark_at_cool-tools.co.uk

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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.com
    --
    Author: Alexander Ordonez
    INET: aordonez_at_ccss.sa.cr

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).
  • Rick_Cale_at_teamhealth.com at Feb 6, 2002 at 6:30 pm
    DELETE FROM a
    WHERE a.rowid > (SELECT MIN(b.rowid)

    FROM table b
    WHERE b.key_field1 = a.keyfield1
    AND b.key_field2 = a.keyfield2
    AND b.key_fieldN = a.keyfieldN);

    Be certain to include all fields in WHERE clause that make up a unique
    record.

    Rick

    Alexander

    Ordonez To: Multiple recipients of list ORACLE-L

    Subject: Duplicate rows

    Sent by:

    root_at_fatcity.

    com

    02/06/2002

    10:33 AM

    Please

    respond to

    ORACLE-L

    Hi gurus,
    I need detect and delete duplicate rows in any table, somebody helpme
    thanks!!!

    @lex

    Lic. Alexander Ordóñez Arroyo
    Caja Costarricense del Seguro Social
    Soporte Técnico - División de Informática
    Telefono: 295-2004, San José, Costa Rica
    Aordonez_at_ccss.sa.cr Icq# 30173325

    The true is out there in WWW

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Alexander Ordonez
    INET: aordonez_at_ccss.sa.cr

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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.com
    --
    Author:
    INET: Rick_Cale_at_teamhealth.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).
  • Alexander Ordonez at Feb 6, 2002 at 6:39 pm
    thanks!!!

    @lex

    Lic. Alexander Ordóñez Arroyo

    Caja Costarricense del Seguro Social
    Soporte Técnico - División de Informática
    Telefono: 295-2004, San José, Costa Rica
    Aordonez_at_ccss.sa.cr Icq# 30173325

    ------------------------------------------------------------

    The true is out there in WWW
    -----Mensaje original-----
    De: Rick_Cale_at_teamhealth.com [SMTP:Rick_Cale_at_teamhealth.com]
    Enviado el: Miércoles 6 de Febrero de 2002 10:26 AM
    Para: ORACLE-L_at_fatcity.com; aordonez_at_ccss.sa.cr
    Asunto: Re: Duplicate rows


    DELETE FROM a
    WHERE a.rowid > (SELECT MIN(b.rowid)
    FROM table b
    WHERE b.key_field1 = a.keyfield1
    AND b.key_field2 = a.keyfield2
    AND b.key_fieldN = a.keyfieldN);

    Be certain to include all fields in WHERE clause that make up a unique
    record.

    Rick





    Alexander

    Ordonez To: Multiple recipients of
    list ORACLE-L

    s.sa.cr> Subject: Duplicate rows

    Sent by:

    root_at_fatcity.

    com





    02/06/2002

    10:33 AM

    Please

    respond to

    ORACLE-L








    Hi gurus,
    I need detect and delete duplicate rows in any table, somebody helpme
    thanks!!!

    @lex
    ------------------------------------------------------------
    Lic. Alexander Ordóñez Arroyo
    Caja Costarricense del Seguro Social
    Soporte Técnico - División de Informática
    Telefono: 295-2004, San José, Costa Rica
    Aordonez_at_ccss.sa.cr Icq# 30173325

    ------------------------------------------------------------
    The true is out there in WWW

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Alexander Ordonez
    INET: aordonez_at_ccss.sa.cr

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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.com
    --
    Author: Alexander Ordonez
    INET: aordonez_at_ccss.sa.cr

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).
  • Joan Hsieh at Feb 6, 2002 at 6:44 pm
    delete from z where rowid in (select rowid from z a where a.rowid >
    (select
    min(rowid) from z b where a.x = b.x));

    Alexander Ordonez wrote:
    Hi gurus,
    I need detect and delete duplicate rows in any table, somebody helpme
    thanks!!!

    @lex
    ------------------------------------------------------------
    Lic. Alexander Ordóñez Arroyo
    Caja Costarricense del Seguro Social
    Soporte Técnico - División de Informática
    Telefono: 295-2004, San José, Costa Rica
    Aordonez_at_ccss.sa.cr Icq# 30173325

    ------------------------------------------------------------
    The true is out there in WWW

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Alexander Ordonez
    INET: aordonez_at_ccss.sa.cr

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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.com
    --
    Author: Joan Hsieh
    INET: joan.hsieh_at_tufts.edu

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).
  • Babich , Sergey at Feb 6, 2002 at 6:46 pm
    Delete from table_name where rowid not in(select min(rowid) from table_name
    group by col1,col2,...coln);
    Best,
    Sergey

    -----Original Message-----
    Sent: Wednesday, February 06, 2002 10:33 AM

    To: Multiple recipients of list ORACLE-L
    Subject: Duplicate rows

    Hi gurus,
    I need detect and delete duplicate rows in any table, somebody helpme
    thanks!!!

    @lex

    Lic. Alexander Ordóñez Arroyo

    Caja Costarricense del Seguro Social
    Soporte Técnico - División de Informática
    Telefono: 295-2004, San José, Costa Rica
    Aordonez_at_ccss.sa.cr Icq# 30173325

    ------------------------------------------------------------

    The true is out there in WWW

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Alexander Ordonez
    INET: aordonez_at_ccss.sa.cr

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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.com
    --
    Author: Babich, Sergey
    INET: SBabich_at_handexmail.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).
  • Van M. Etheridge at Feb 6, 2002 at 6:57 pm
    Delete duplicate records

    Sanjay Raj
    06 Dec 2001, Rating 4.42 (out of 5)
    Here is a short Oracle script that deletes duplicate records from a table based on the unique columns selected:
    Rem del_dup.sql
    Rem
    Rem Script to delete duplicate rows from a table
    Rem
    Rem Enter Table_Name as MY_TABLE
    Rem And Column_List as COLUMN_1,COLUMN_2,COLUMN_3 ... Column_N

    Rem                   (i.e. no spaces)

    Rem
    Rem
    Accept table_name Prompt 'Enter Table Name: '
    Accept column_list Prompt 'Enter Column List (no spaces): '
    BEGIN

    LOOP

    DELETE FROM &table_name
    WHERE ROWID IN (SELECT MIN (ROWID)
    FROM &table_name
    GROUP BY &column_list
    HAVING COUNT (*) > 1);
    EXIT WHEN SQL%NOTFOUND;

    END LOOP;

    COMMIT;

    END;

    /

    -----Original Message-----
    From: Alexander Ordonez
    Sent: Wednesday, February 06, 2002 10:33 AM
    To: Multiple recipients of list ORACLE-L
    Subject: Duplicate rows

    Hi gurus,
    I need detect and delete duplicate rows in any table, somebody helpme
    thanks!!!

    @lex

    Lic. Alexander Ordóñez Arroyo
    Caja Costarricense del Seguro Social
    Soporte Técnico - División de Informática
    Telefono: 295-2004, San José, Costa Rica
    Aordonez_at_ccss.sa.cr        Icq# 30173325

    The true is out there in WWW

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Alexander Ordonez
    INET: aordonez_at_ccss.sa.cr

    Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
    San Diego, California        -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).
  • Orantdba at Feb 6, 2002 at 7:03 pm
    Hi,

    Probably the best way is to attempt to enable a unique constraint and
    use the exceptions table to find the rowid's all rows that violate the
    constraint.

    John

    aordonez_at_ccss.sa.cr wrote:
    Hi gurus,
    I need detect and delete duplicate rows in any table, somebody helpme
    thanks!!!

    @lex
    ------------------------------------------------------------
    Lic. Alexander Ordóñez Arroyo
    Caja Costarricense del Seguro Social
    Soporte Técnico - División de Informática
    Telefono: 295-2004, San José, Costa Rica
    Aordonez_at_ccss.sa.cr Icq# 30173325

    ------------------------------------------------------------
    The true is out there in WWW
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: orantdba
    INET: orantdba_at_netscape.net

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).
  • Rachel Carmichael at Feb 6, 2002 at 8:14 pm
    um yeah, that works too :)

    orantdba wrote:
    Hi,

    Probably the best way is to attempt to enable a unique constraint and

    use the exceptions table to find the rowid's all rows that violate
    the
    constraint.

    John

    aordonez_at_ccss.sa.cr wrote:
    Hi gurus,
    I need detect and delete duplicate rows in any table, somebody helpme
    thanks!!!

    @lex
    ------------------------------------------------------------
    Lic. Alexander Ordóñez Arroyo
    Caja Costarricense del Seguro Social
    Soporte Técnico - División de Informática
    Telefono: 295-2004, San José, Costa Rica
    Aordonez_at_ccss.sa.cr Icq# 30173325

    ------------------------------------------------------------
    The true is out there in WWW

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: orantdba
    INET: orantdba_at_netscape.net

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    --------------------------------------------------------------------
    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).
    Do You Yahoo!?
    Send FREE Valentine eCards with Yahoo! Greetings!
    http://greetings.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 6, '02 at 3:57p
activeFeb 6, '02 at 8:14p
posts14
users10
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase