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
Technology Geek 