how do u fnd the duplicate rows and how to delete the duplicate rows?

how do u fnd the duplicate rows and how to delete the
duplicate rows?

Question Posted / sree

2 Answers
7335 Views
IBM, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

how do u fnd the duplicate rows and how to delete the duplicate rows?..

Hi Sankar...
The methods as told by u r correct but i guess sree has
asked about finding duplicate records and the way to delete
them in oracle not in informatica.

So, the query to find out the duplicate records in a given
table (e.g. emp table) is as below:

select * from emp where empno in (select empno from emp
group by empno where count(*) >1);

Explanation:
============
See, in EMP table there are total 14 records out of which
only empno is unique,rest all fields can be repeated, so the
empno which are repeated more than once are duplicate
records & the above query shows the same.

Query to delete duplicate records;

delete from emp where rowid not in (select max(rowid) from
emp group by empno);

OR

delete from emp where rowid not in (select min(rowid) from
emp group by empno);

OR

delete from emp e1 where rowid < (select max(rowid) from emp
e2 where e1.empno=e2.empno);

OR

delete from emp e1 where rowid > (select min(rowid) from emp
e2 where e1.empno=e2.empno);

Is This Answer Correct ?

21 Yes

3 No

how do u fnd the duplicate rows and how to delete the duplicate rows?..

Answer / sankar

repet(more then one time) the rows is duplicate rows,
there r many ways to dele the duplicate rows
1.in source qualifies t/r select the DISTINC option
2.in aggreter t/r select the group by port
3.in sorter t/r select the distinc

Is This Answer Correct ?

5 Yes

4 No

Post New Answer