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 [mailto:[EMAIL PROTECTED]]
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
[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).
