delete a duplicate rows in a oracle table

 
Check the below Steps to delete duplicate row : 
 
 

SQL>select * from emp;


EMPID ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
10005 palani 54544 10
10001 Scott Tiger 1000 40
10002 Frank Naude 500 20
10003 rajesh 21000 140
10004 ramesh 3500 220
10005 suresh 34567 123
 
 

delete duplicate rows in empid column in emp table

 
 

 SQL>delete from emp where rowid not in (select max(rowid) from emp group by empid);


1 row deleted.

SQL>select * from emp;


EMPID ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
10001 Scott Tiger 1000 40
10002 Frank Naude 500 20
10003 rajesh 21000 140
10004 ramesh 3500 220
10005 suresh 34567 123
 
 
to delete the old duplicate row from the table instead of max(rowid) replace min(rowid) 
for exampl
 

SQL>insert into emp values(10005,’Osama’,54544,10);


1 row created.

SQL>select * from emp;


EMPID ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
10005 Osama 54544 10
10001 Scott Tiger 1000 40
10002 Frank Naude 500 20
10003 rajesh 21000 140
10004 ramesh 3500 220
10005 suresh 34567 123

6 rows selected.

SQL>delete from emp where rowid not in (select min(rowid) from emp group by empid);


1 row deleted.
 

SQL>select * from emp;


EMPID ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
10005 Osama 54544 10
10001 Scott Tiger 1000 40
10002 Frank Naude 500 20
10003 rajesh 21000 140
10004 ramesh 3500 220
 
 
 
thank you 
Osama mustafa 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.