-1

what is logic behind deletion of duplicate rows? I got know this is the query which is used to delete duplicate rows. 

delete from tvsemp e where rowid>(select min(rowid) from tvsemp m where e.ename=m.ename);

Here when I divided the subquery i.e first select min(m.rowid) from tvsemp e, tvsemp m where e.ename=m.ename; Then I get result as the rowid is 

MIN(M.ROWID)
___________________
AAAEDUAABAAAKiqAAP

Then for the outer query select ename from tvsemp emp where rowid>'AAAEDUAABAAAKiqAAP' But for this I am getting n-1 results(except the one which I eliminated using '>' sin)

My doubt is if this two are combined I am getting exact table without duplicate why is this?

Chay
  • 11
  • 8

1 Answers1

3

Are you asking if the first query will work?

delete from tvsemp e
where rowid>(select min(rowid) from tvsemp m where e.ename=m.ename);

The answer is "yes".

As for how it works, your subquery is a correlated subquery, which basically means it references the outer query. Oracle executes the subquery for each row of tvsemp e, and the e.ename = m.ename in the subquery limits the subquery's results to rows where ename equals the ename in the outer query's current row.

Getting n - 1 results is exactly what you want: if there are four rows with the name Chay, you want to delete three of them.

Put another way, if there are four rows with the name Chay, only one of them will have the minimum ROWID value. The ROWID for the other three will be higher than the minimum, so the DELETE query will delete them.

I hope this makes sense. There's more information about subqueries (including correlated subqueries) here.

Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
  • Thank you Gibbs that was helpful. Do you mean the corelated subquery acts as loops. i.e it will process each row upto the loop terminates with no row ? – Chay Aug 19 '13 at 17:24
  • There's a loop, but the outer query (`from tvsemp e`) controls it: the subquery is called for each row in the outer query. It's hard to explain. The link I gave in the answer has some examples that may help. – Ed Gibbs Aug 19 '13 at 17:37
  • ok No Problem will go through the documentation once. Thanks you – Chay Aug 19 '13 at 17:44