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

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

More Oracle General Interview Questions

How to retrieve the count of updated rows?

0 Answers  


How to view all columns in an existing table?

0 Answers  


how can find the second max sal for every group(i.e i want group the data based on key and find the second max sal for every group

2 Answers   Verizon,


Hi All, Recently I attended a interview for a developer position in an educational university. They asked me the a question, I answered somehow but I was not not satisfied myself. Can anybody reply for it? The question is: A business user comes to you directly with an urgent request:: there is a problem with some students not being able to re-enrol. It appears that ?something has gone wrong with the end-dating of some previous courses they had enrolled in, and this is preventing them from completing their on-line re-enrolments?. Fortunately the user has done some analysis on the situation, and has developed some code to change the end dates. They tell you that they have tested this code in the ?dev? environment, and it works fine. They are asking you to please get this implemented in production as soon as possible. What steps would you take in response to their request ? Thanks

2 Answers  


How to shutdown your 10g xe server from command line?

0 Answers  






How can you tell how much space is left on a given file system and how much space each of the file systems subdirectories take-up?

0 Answers  


How to do paging with oracle?

0 Answers  


Hi how Can I Add A Foreign key that references a table that has composit primary key ? example i had costumer table that has C_Id and SSN Both as PK and another table has C_Id that must refernece C_Id in Customers i done the usual way and got oracle error message about uniqeness any ideas plz

2 Answers  


Will you be able to store pictures in the database?

0 Answers  


How to use like conditions in oracle?

0 Answers  


Explain integrity constraints?

0 Answers  


How do I uninstall oracle 11g?

0 Answers  


Categories
  • Oracle General Interview Questions Oracle General (1789)
  • Oracle DBA (Database Administration) Interview Questions Oracle DBA (Database Administration) (261)
  • Oracle Call Interface (OCI) Interview Questions Oracle Call Interface (OCI) (10)
  • Oracle Architecture Interview Questions Oracle Architecture (90)
  • Oracle Security Interview Questions Oracle Security (38)
  • Oracle Forms Reports Interview Questions Oracle Forms Reports (510)
  • Oracle Data Integrator (ODI) Interview Questions Oracle Data Integrator (ODI) (120)
  • Oracle ETL Interview Questions Oracle ETL (15)
  • Oracle RAC Interview Questions Oracle RAC (93)
  • Oracle D2K Interview Questions Oracle D2K (72)
  • Oracle AllOther Interview Questions Oracle AllOther (241)