Home » RDBMS Server » Server Administration » regarding primary key,deleteing duplicate rows
regarding primary key,deleteing duplicate rows [message #371315] Tue, 03 October 2000 04:04 Go to next message
h.k.n murthy
Messages: 1
Registered: October 2000
Junior Member
how to delete duplicate rows by using rownum?
how to disable index while creating primary key with ne table?
Re: regarding primary key,deleteing duplicate rows [message #371317 is a reply to message #371315] Tue, 03 October 2000 07:30 Go to previous messageGo to next message
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 :)
Re: regarding primary key,deleteing duplicate rows [message #371436 is a reply to message #371317] Thu, 19 October 2000 04:04 Go to previous message
Anju
Messages: 33
Registered: September 2000
Member
I agree with the query by Prem ,but still u can
delete duplicate rows using this query also..
Assuming our favourite emp table containing duplicate rows ..

delete from emp
where rowid not in (
select max(rowid) from emp
group by empno)
Previous Topic: OLE2
Next Topic: Top n rows
Goto Forum:
  


Current Time: Thu Mar 28 03:45:38 CDT 2024