how to delete all duplicate records from a table using
subquery?
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 execute a stored program unit?
What is the difference between formal parameters and actual parameters?
Maximum how many triggers can be updated in table ?
5 Answers Accenture, Cap Gemini,
How remove data files before opening a database?
How to use like conditions in oracle?
What happens if recursive calls get out of control?
How to select the name of employee who is getting maximum sal with out using subquery
What are the different types of failures that occur in Oracle database?
How to rename an index?
dear sir/madam, i have done my engg in 2007 after that i did oracle course (sql,plsql,forms,reports) .from 2008 to 2009 i searched job but didnt got.so i planned to move to sales field & i gone to dubai.till now i m in sales only.now i want to shift my career in to IT industry.but now people are not hiring as fresher.so i planned put fake 1 + exp. is it good or bad or any other way is there to enter in IT.please suggest me or call me 09632087338.
what is the bitmap index?
When system tablespace is created?