Write an SQL Query to
Delete Duplicate records from a table using ROWID.
- 24 Answers
- 74145 Views
- pspl, Satyam, Thinkways, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / rajesh
Delete from table_name where ROWID not in ( select
max(rowid) from table group by duplicate_values_field_name);
Regards
Rajesh...
If any queries..then fwd ...baburajeshd@gmail.com
| Is This Answer Correct ? | 91 Yes | 39 No |
Answer / veera
delete from tablename where rowid not in(select min(rowid)
from table name group by duplicate record col)
| Is This Answer Correct ? | 37 Yes | 21 No |
Answer / swati
DELETE FROM <table_name> T1
WHERE T1.ROWID > (SELECT MIN(T2.ROWID)
FROM <table_name> T2
WHERE T2.<common column name> = T1.<common
column name>)
----------------------------
DELETE FROM <table_name> T1
WHERE T1.ROWID < (SELECT MAX(T2.ROWID)
FROM <table_name> T2
WHERE T2.<common column name> = T1.<common
column name>)
| Is This Answer Correct ? | 19 Yes | 5 No |
Answer / arjun
delete from employ where rowid not in(select min(rowid) from
employ group by id,ename,age);
Arjun
| Is This Answer Correct ? | 17 Yes | 7 No |
Answer / amit bhatnagar
Swati is cool. buth her queries are correct. you can either
take rowid >with subquery fetching minimum (rowid) else
Rowid < with subquery fething maximum (row id)
DELETE FROM <table_name> T1
WHERE T1.ROWID > (SELECT MIN(T2.ROWID)
FROM <table_name> T2
WHERE T2.<common column name> = T1.<common
column name>)
----------------------------
DELETE FROM <table_name> T1
WHERE T1.ROWID < (SELECT MAX(T2.ROWID)
FROM <table_name> T2
WHERE T2.<common column name> = T1.<common
column name>)
Good job Swati.. :)
| Is This Answer Correct ? | 15 Yes | 6 No |
Answer / venki_discussions
delete from table (table name.a) where
rowid not in(select min(rowid) from (table name.b)
group by (column name);
| Is This Answer Correct ? | 8 Yes | 1 No |
Answer / koti
delete from emk tl
where tl.rowid >
( select min(tl2.rowID) from emk tl2
where tl.empno = tl2.empno
and tl.ename = tl2.ename)
Madhuapps,is alsow right...
| Is This Answer Correct ? | 16 Yes | 10 No |
Answer / samad
delete from emp a where rowid >(select min(rowid) from emp
b where a.rowid = b.rowid);
2. DELETE FROM EMP A
WHERE ROWID > ( SELECT min(ROWID) FROM EMP B WHERE
A.ROWID = B.ROWID)
cheers
samad
| Is This Answer Correct ? | 21 Yes | 18 No |
Answer / pnr kiran
DELETE FROM TABLE E
WHERE COLUMN_NAME IN ( SELECT COLUMN_NAME FROM TABLE D
WHERE D.COLUMN_NAME_1=E.COLUMN_NAME_1
MINUS
SELECT COLUMN_NAME FROM TABLE F
WHERE F.COLUMN_NAME_1 = E.COLUMN_NAME_1
AND ROWNUM = 1);
| Is This Answer Correct ? | 4 Yes | 1 No |
Answer / sushmitha
This link provides different methods of removing duplicate
rows from table
http://www.besttechtools.com/SQLArticles.aspx?
ID=DeleteDuplicate
| Is This Answer Correct ? | 4 Yes | 1 No |
where is chandra institute in hyderabad for oracle apps technical training ?
how to move the one file from one instance to another instance?and ur scripts also?
what is difference between d2k report and oracle apps report
10 Answers Seven Hills, Wipro,
how to make a parameter optional in the query?
what is the difference between multiorg views and multiorg tables
2 Answers Immense Source, Intelligroup, TCS,
Can we create tables in apps schema?
How to create a purchase order without a requisition?
diff between key and descriptive flexfield.
in one report using two layout models.if yes how to do
Tell me some interface tables on ordermangement.
Are you familiar with internet architecture of oracle apps 11i?
q)what are the validations in sql*loader
Oracle Apps Technical 
