Search code examples
sqloracle-databaserowid

logic behind deletion of duplicate rows?

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?

Solution

  • 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.