How to delete duplicate records if we have huge volume of
records in a table ?
(rowid is not the correct approach)
- 6 Answers
- 8447 Views
- Puma, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / keyrun
Hi, There are many ways to delete the duplicates!
By Using the following Transformations you can delete the Duplicates:
Source Qualifier: Use SQ and check 'Distinct' property (If the Source is Relational)
Sorter: Use sorter and in Properties tab Check 'Distinct'(If the Source is Flat File)
Aggregator: Use Aggregator & Group by on the key port.
| Is This Answer Correct ? | 7 Yes | 0 No |
Answer / chandu
select * from from <table_name> where rowid not in(select
min(rowid) from <table_name> group by column(primary or
unique key column))
| Is This Answer Correct ? | 5 Yes | 0 No |
First we can count the no of records using aggregator
Transformation with group by (port) for ex, in emp table
take empno. Second we can take the Filter condition like
count=1. Other records are rejected not loaded into the target.
Hope it will help little bit...
| Is This Answer Correct ? | 1 Yes | 1 No |
Answer / cmanojkumar
Hi,
Thanks for your answer.
I am sorry this question i need to post in oracle forum not
informatica.
Any way your answer is usefull for me.
Could you please tell me how can we delete with our rowid in
oracle ?
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / ravikumar2614
DELETE FROM EMP E1 WHERE ROWID<
(SELECT MAX(ROWID) FROM EMP E2 WHERE E1.ROWID=E2.ROWID)
IF I AM WRONG PLEASE CORRECT ME ON ravi.info2614@gmail.com
| Is This Answer Correct ? | 1 Yes | 1 No |
Answer / dilip ingole
Delete from emp where rowid not in(select min(rowid) from emp group by eid,ename)
in group by column you need mention all column in table
| Is This Answer Correct ? | 0 Yes | 0 No |
Examples of Fatal & Non-Fatal error?
What is informatica powercenter repository?
Is it possible to create multiple domains on a single Informatica server?
a flat file having 1 laks records. if i want convert excel file what happen. (bcz a exccel sheet having 65536 colums but flat files one lak columns). how to get one laks colums in excel sheet .
how many new transformations are introduced in 8.1 which are not available in 7.1? any new transformation in 8.6?
How does a rank transform differ from aggregator transform functions max and min?
Hello...... How can i set my mapping as read-only? Please answer me. Advance thanks
What are parallel querys and query hints?
How to recover sessions in concurrent batches?
suppose in my source flatfile i have 10000 records in that some of master table records missed table records missed then what will u do
What do you understand by SOA of Informatica?
Hi experts ,I have a Source of 1000 records.I have to load 100 records in first target,101 to 200 records in 2nd target, 201 to 300 records in 3rd target and again 301 to 400 in first target like that as cyclick process. How can i acheive this.
Teradata 
