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 |
when we are importing items in inventory, showing errors, Oracle support suggested us for running scripts & also suggested if we run scripts, iprocurement applicaation if is there it will show shared and if we go in future for iprocurement, it wont work. So kinldy suggest any functional solution.
How to assign a table row to a record variable?
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
Respected sir, Please send me technical questions related to oracle apps..
What are the factors that affect OPTIMIZER in choosing an Optimization approach ?
What are a query and state the different types of queries and their uses?
How do I find the database name in oracle?
What happens to indexes if you drop a table?
what is the use of ondelete cascade?
How to bring a tablespace offline?
How to Identify the previously inserted/updated records in already populated table.
How to speed up webrick?