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 |
what are the different types of parameters avialable in reports?
What is the package?
What is SET-OF-BOOKS?
maximum how many parameters we can pass through pl/sql stored procedure to oracle apps?how many mandatory parameters we can pass through pl/sql program to oracle apps11i?
WHAT IS USE OF APPS API?
How to retry multiple errored workflow processes? What is the access level in workflow used for? How do you define start and end functions in workflow? How does they differ from normal functions? Give me some workflow tables? What is the difference between a function and notification in workflow? I have sent two different notifications to two different users and I want to wait till both they are approved to send 3rd notification. How can you achieve it? What is item type and item key in workflow? How do you use attribute values in workflow messages? How do you use lookups in workflow? What are roles in workflow and how they are used? How do you download or upload a workflow from a server? What are steps to customize the workflow? What functions can you perform from workflow administrator responsibility? To send an email to the user workflow notification is the only way or is there any other ways to send it? Give me some workflow standard procedures? How can you run/start/kickoff workflow? What is wf_engine package used for? How many processes can each workflow contain? What is Runnable option in workflow? At what level it exists? What are different types of attributes in workflow? How do you reassign a notification? What is process in workflow? How can you send direct oracle form link through workflow notifications? How can you send a notification to multiple users? Can you change the list dynamically? Can you send html code in workflow notification? I have sent two different notifications to two different users and I want to wait till atleast one is approved to send 3rd notification. How can you achieve it?
explain o2c cycle? what are the table reflected for this process?
Are you familiar with internet architecture of oracle apps 11i?
How to create multi-layout reports ? If we have 5 layouts , then how to get into the 4th one ?
How to link modules in apps
what type of testing u r doing?
What are triggers?