Sunday, 7 February 2016

Query to delete duplicate rows from the table in Oracle

Requirement: Write a query to delete duplicate rows from the table in Oracle.

Solution: For solution of the above requirement, let us create a table FRIENDS, and insert few records in FRIENDS table as shown below:

create table friends (friend_id number(2),friend_name varchar2(20));

insert into friends values(1, 'Subhashini');
insert into friends values(2, 'Somya');
insert into friends values(3, 'Madhupal');
insert into friends values(1, 'Subhashini');
insert into friends values(4, 'Sharique');
insert into friends values(5, 'Rajesh');
insert into friends values(4, 'Sharique');
insert into friends values(6, 'Saurab');
insert into friends values(7, 'Bijoy');
insert into friends values(8, 'Pankaj');

Thus we have the below records in the FRIENDS table

In the above result we can see that there are few duplicate rows/records.

Now if we want to delete the duplicate records from the above FRIENDS table there are numerous ways. Few of them are discussed below:

1.       Delete the duplicate records using rowid.

DELETE
FROM friends
WHERE rowid NOT IN
  (SELECT MAX(rowid) FROM friends GROUP BY friend_id
  );


2.       Delete the duplicate records using self join.

DELETE
FROM friends f1
WHERE rowid NOT IN
  (SELECT MAX(rowid) FROM friends f2 WHERE f1.friend_id = f2.friend_id
  );


3.       Delete the duplicate records using row_number()

DELETE
FROM friends
WHERE rowid IN
  (SELECT row_id
  FROM
    (SELECT rowid row_id,
      row_number() over(partition BY friend_id order by friend_name) row_no
    FROM friends
    )
  WHERE row_no > 1
  );

 
4.        Delete the duplicate records using dense_rank ()

DELETE
FROM friends
WHERE rowid IN
  (SELECT row_id
  FROM
    (SELECT rowid row_id,
      dense_rank() over(partition BY friend_id order by rowid) row_no
    FROM friends
    )
  WHERE row_no > 1
  );
 

Thus, the solution to our requirement.

If you like the post please comment, share, and like me on Facebook.


Thanks & Regards,
Susanto Paul