Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 12 August 2013

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

0 comments:

Post a Comment