Monday, August 31, 2015

How to delete duplicate records and retain one record among duplicates?

Scenario:
If we are having 5 duplicate records then you have to delete 4 records and retain 1 record.

Process:
Execute Following script:

CREATE TABLE TestTable(
Code Varchar2(1) ,
n number(2)
)
/
Insert into TestTable (CODE,N) values ('A',1);
Insert into TestTable (CODE,N) values ('A',2);
Insert into TestTable (CODE,N) values ('A',2);
Insert into TestTable (CODE,N) values ('B',1);
Insert into TestTable (CODE,N) values ('B',2);
Insert into TestTable (CODE,N) values ('B',3);
Insert into TestTable (CODE,N) values ('C',1);
Insert into TestTable (CODE,N) values ('C',1);
Insert into TestTable (CODE,N) values ('C',2);
/

Query to delete duplicate records and retain one record:

DELETE FROM TESTTABLE WHERE ROWID IN (SELECT ROWID FROM (
SELECT RN,ROWID FROM (
SELECT CODE,N,ROW_NUMBER() OVER(PARTITION BY CODE,N ORDER BY CODE DESC) RN
FROM TESTTABLE) WHERE RN>1));
/



No comments:

Post a Comment