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 [EMAIL PROTECTED] Icq# 30173325
------------------------------------------------------------ The true is out there in WWW > -----Mensaje original----- > De: Mark Leith [SMTP:[EMAIL PROTECTED]] > 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 <OWNER>.<TABLE_NAME> > group by COL1, COL2, COL# > having count(*) > 1; > > Remove duplicate records: > > delete from <OWNER>.<TABLE_NAME> a > where rowid < ( > select max(rowid) > from <OWNER>.<TABLE_NAME> 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 > [EMAIL PROTECTED] Icq# 30173325 > > ------------------------------------------------------------ > The true is out there in WWW > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Alexander Ordonez > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
