|
|
| Re: regarding primary key,deleteing duplicate rows [message #371317 is a reply to message #371315] |
Tue, 03 October 2000 07:30   |
Prem
Messages: 79
Registered: August 1998
|
Member |
|
|
To answer your question, i'll use the ever famous EMP table. Assume it has only empno and ename fields and no primary key is defined. So empno can have duplicate values. And our mission is to flush these duplicates out. right?
delete from emp a where rowid not in (select rowid
from emp b where a.empno = b.empno and rownum = 1)
The above query leaves only one record having a given empno and removes all the repeating ones.
It uses the corelated sub query and for every record selected in the outer query, the inner query is executed. Thats the reason we have to use the alias to differenciate between the inner and the outer emp table.
hth
Prem :)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
