Delete Duplicate Records in Oracle

Delete Duplicate Record in Oracle 

   Deleting Duplicate Records , if all column values are repeated,

   

Suppose 

Create table called Sports

 

SQL>create table sports(ID number(2),name varchar2(20),country char(2));

Insert records into Sports table

SQL>insert into sports values(1,'cricket','IN');

SQL> insert into sports values(2,'cricket','UK');

SQL> insert into sports values(3,'Hand FootBall','US');

SQL> insert into sports values(4,'Base Ball','US');

Display Records 

SQL> Select * from Sports

ID NAME                 Co
-- -------------------- --
 1 cricket              IN
 2 cricket              UK
 3 Hand FootBall        US
 4 Base Ball            US

Re-insert same values into Sports table

SQL> Insert into Sports Select * from sports

4 rows created.

Display Records again

SQL> Select * from sports;

ID NAME                 CO
-- -------------------- --
 1 cricket              IN
 2 cricket              UK
 3 Hand FootBall        US
 4 Base Ball            US
 1 cricket              IN
 2 cricket              UK
 3 Hand FootBall        US
 4 Base Ball            US

8 rows selected.

Deleting Duplicate Records from Sports Table.

SQL>delete from sports where rowid in (select max(rowid) from sports group by id);

Display Records again, it has all unique rows,

SQL> Select * from Sports

ID NAME                 Co
-- -------------------- --
 1 cricket              IN
 2 cricket              UK
 3 Hand FootBall        US
 4 Base Ball            US

Tags:Delete Duplicate Records in Oracle,How to delete duplicate values in oracle table, remove duplicate values in oracle, oracle insert,oracle create table, oracle insert using select,