Script: To Delete Duplicate rows from a table based on column values
This script will take a table name and list of columns as
input which you desire to be unique and deletes all rows with the same value in
these columns, leaving only the row with the minimum rowid. This script does
not deal with NULL values in the columns.
Restrictions: This
script will not work if the column(s) to be filtered on having a datatype of
LONG, LONG RAW, RAW, CLOB, NCLOB, BLOB, BFILE or an object datatype. It will
delete duplicate rows from a table based on supplied columns from a table.
REM Either you save it into a file "delete_dup.sql"
and run it later from SQL*plus or you can directly run the
below script without saving it into file.
set echo off
set verify off heading off
undefine t
undefine c
prompt
prompt
prompt Enter name of table with duplicate rows
prompt
accept t prompt 'Table: '
prompt
select 'Table '||upper('&&t') from dual;
describe &&t
prompt
prompt Enter name(s) of column(s) which should be
unique. If you want to provide more
prompt than one column, you must separate them with
commas.
prompt
accept c prompt 'Column(s): '
prompt
delete from &&t
where rowid not in (select min(rowid) from
&&t group by &&c)
/
Example:
Create table EMP(EMP number(5), NAME VARCHAR2(30),
SALARY number (10,2));
DESC EMP;
insert into EMP values(1, 'SHAHID', 6000);
insert into EMP values(1, 'SHAHID', 6000);
insert into EMP values(1, 'SHAHID', 6000);
insert into EMP values(1, 'KHALID', 6000);
insert into EMP values(2, 'SHAHID', 6000);
insert into EMP values(3, 'SHAHID', 4000);
EMP NAME SALARY
----- ---------- -------
1 SHAHID 6000
1 SHAHID 6000
1 SHAHID 6000
1 KHALID 6000
2 SHAHID 6000
3 SHAHID 4000
When you run the above script either thruogh file or
directly it will prompt you.
SQL> @"D:\Backup\Scripts\delete_dup.sql"
Enter name of table with duplicate rows
Table: emp
1 row selected.
Name Null? Type
------------------- ----- --------------
EMP NUMBER
(5)
NAME VARCHAR2
(30)
SALARY NUMBER
(10,2)
Enter name(s) of column(s) which should be unique.
If you want to provide more than one column, you must
separate them with commas.
Column(s): EMP,SALARY
3 row deleted.
SELECT * FROM EMP;
EMP NAME SALARY
----- ----------- -------
1 SHAHID 6000
2 SHAHID 6000
3 SHAHID 4000
