Write an SQL Query to
Delete Duplicate records from a table using ROWID.
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 |
HOW CAN U KNOW TABLE IS REGISTERD IN APPS?
What is OM Cycle and What do u mean by Alternate Item
Can we create tables in apps schema?
where to define interorganization transaction charges and what are the different options?
what is the differences between internal requisition and internal sales order?
How to write the no data found in XML Publiser Report in apps?
What is the most critical part which you had faced in your project ?
1 Answers LG, Satyam, Tech Mahindra,
Where I can download D2K software?
What is the Use of User Exit in Oracle apps and when we are developeing the new report you need to pass one parameter called P_CONC_REQUEST_ID,what is the use of this parameter and is it mandatory.Can't we develop report without this User exit's and P_CONC_REQUEST_ID parameter
How to call oracle database stored procedure from form in form coding ?
What is invoice?
In developer Reports::there are 3 parameters.can you pass the paramter1+paratmeter2 value to the third parameter??