Home » SQL & PL/SQL » SQL & PL/SQL » Delete duplicate rows
Delete duplicate rows [message #30850] Wed, 26 May 2004 22:47 Go to next message
Vinayak Awasthi
Messages: 19
Registered: October 2001
Junior Member
Hi All,

I want to delete duplicate rows from my table without using rowid concept. Can anyone share some other query?

Regards
Vinayak
Re: Delete duplicate rows [message #30851 is a reply to message #30850] Wed, 26 May 2004 23:08 Go to previous messageGo to next message
Nazir
Messages: 1
Registered: May 2004
Junior Member
Try this query

delete from t_temp where rowid not in (select rowid from t_temp a where rowid=(select rowid from t_temp where name=a.name and rownum=1))
Re: Delete duplicate rows [message #30868 is a reply to message #30850] Thu, 27 May 2004 06:38 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
Now, Vinayak, why is "the ROWID concept" off the table?

I always want to know, but have not yet found out, where these bizarre requirements of not using Oracle's own features come from?

You might as well ask, "How can I find the top salary earners for each department without using SELECT?"

A.
Re: Delete duplicate rows [message #30877 is a reply to message #30868] Thu, 27 May 2004 19:31 Go to previous messageGo to next message
Vinayak Awasthi
Messages: 19
Registered: October 2001
Junior Member
Dear AM,

This is not a bizarre requirements which I had asked to sought out. I just wanted to know a different way of doing the same thing.I would have never asked such a thing if the industry people can refrain themselves from asking such question in an interview.

Anyway thanks for your reply.

Regards
Vinayak
Re: Delete duplicate rows [message #30884 is a reply to message #30877] Fri, 28 May 2004 03:48 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
Vinayak,

Thanks for the clarification. I couldn't tell from the original post whether this constraint was a business requirement or an academic exercise. I had assumed it was the former.

Art.
Re: Delete duplicate rows [message #30899 is a reply to message #30851] Fri, 28 May 2004 10:57 Go to previous messageGo to next message
Srinivas Venna
Messages: 1
Registered: May 2004
Junior Member
If you dont want to use ROWID, you can try this work around.
SQL> create table table_name2 as select distinct * from table_name1;
SQL> drop table_name1;
SQL> rename table_name2 to table_name1;
Re: Delete duplicate rows [message #184998 is a reply to message #30850] Sat, 29 July 2006 12:40 Go to previous messageGo to next message
mohan_krishnan83
Messages: 10
Registered: July 2006
Junior Member
To Find the duplicate rows without rowid concept

select column_name,count(column_name) from Table_name
group by column_name having
count(column_name) > 1
SELECT empcode,count(emplcode) from duptemp
group by emplcode having count(emplcode) >1


Regards
Mohana Krishnan R
ACCL
Re: Delete duplicate rows [message #185142 is a reply to message #184998] Mon, 31 July 2006 03:21 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
To Find the duplicate rows without rowid concept
select column_name from (select column_name, row_number() over(partition by key_column1,key_column2 order by  key_column1,key_column2) rn from table_name) where rn >1

Previous Topic: Help Needed Regarding the following Query
Next Topic: Query
Goto Forum:
  


Current Time: Sun Dec 11 02:02:53 CST 2016

Total time taken to generate the page: 0.14086 seconds