how to delete all duplicate records from a table using subquery?

Categories >> Software >> Databases >> Oracle >> Oracle General
Suggest New Category

how to delete all duplicate records from a table using
subquery?

Question Posted / nagireddyunix

7 Answers
21943 Views
ABC, Cap Gemini, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

how to delete all duplicate records from a table using subquery?..

Answer / dharmendra

delete from emp e1 where rowid >(select min(rowid) from emp
e2 where e1.empid=e2.empid)

Is This Answer Correct ?

12 Yes

5 No

how to delete all duplicate records from a table using subquery?..

Answer / maninder

delete from abc where rowid not in (select max(rowid) from
abc group by column_name_with_dup.values.);

Is This Answer Correct ?

10 Yes

6 No

how to delete all duplicate records from a table using subquery?..

Answer / noor

DELETE FROM T1
WHERE ROWID IN (SELECT ROWID FROM T1
WHERE ROWID NOT IN(SELECT MAX(ROWID) FROM
T1 GROUP BY C1,C2));

Is This Answer Correct ?

1 Yes

2 No

how to delete all duplicate records from a table using subquery?..

Answer / edara satish

delete from
<<table_name>>
where rowid in
(select a.rowid
from <<table_name a,table_name b>>
where a.colname = b.colname
b.colname2 = b.colname2
......
...
a.colnamen = colnamen
order by .... desired colnames)

Is This Answer Correct ?

2 Yes

5 No

how to delete all duplicate records from a table using subquery?..

Answer / lingareddy

by using below sub query delete duplicate all records:

DELETE FROM dept WHERE salary IN (
SELECT salary FROM dept GROUP BY salary HAVING ( COUNT(salary) > 1 ))

here is dept is the table name
salary is the column name

for any doubts about SQL contact with me

Thanks & Regards
Lingareddy.S

Is This Answer Correct ?

0 Yes

3 No

how to delete all duplicate records from a table using subquery?..

Answer / aseem k

Tried and tested:
Table d:
7
8
7

DELETE FROM D WHERE ROWID IN (
SELECT DISTINCT MIN(ROWID) FROM D
WHERE DEPTNO IN
(SELECT DEPTNO FROM D GROUP BY DEPTNO
HAVING COUNT(DEPTNO)>1))

Is This Answer Correct ?

1 Yes

5 No

how to delete all duplicate records from a table using subquery?..

Answer / sunil

delete emp where empno not in(select distinct empno from emp);

For example we have duplicates in empno column then write
query like above...whatever column u want u can write..

Is This Answer Correct ?

0 Yes

11 No

Post New Answer