OraFAQ Forum: Server Administration » Is there a way to remove duplicates from a table?

Home » RDBMS Server » Server Administration » Is there a way to remove duplicates from a table?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is there a way to remove duplicates from a table? [message #373514]

Fri, 20 April 2001 07:50

shad
Messages: 50
Registered: October 2000

Member

I found that my database is duplicated. Is there a way to remove these duplicates by using sql? I know I can truncate the tables and import all tables. But I want to do it via sql.
thanks

Report message to a moderator

Re: Is there a way to remove duplicates from a table? [message #373515 is a reply to message #373514]

Fri, 20 April 2001 08:03

Jaco
Messages: 23
Registered: August 2000

Junior Member

select distinct col names from table_name

Report message to a moderator

Re: Is there a way to remove duplicates from a table?(not in a query (distinct) [message #373530 is a reply to message #373515]

Sat, 21 April 2001 04:43

shad
Messages: 50
Registered: October 2000

Member

Sorry. I am not talking about removing duplicates when quering. I am talking about removing duplicates from an Oracle database. I want to write a scirpt tahta will go and find all duplicates , and then remove them so that the database will no longer be duplicated.
thanks , any way

Report message to a moderator

Re: Is there a way to remove duplicates from a table? [message #373531 is a reply to message #373515]

Sat, 21 April 2001 08:45

sajan
Messages: 16
Registered: April 2001

Junior Member

assume a table emp with coulumns - empno, name ...

if u want to delete the duplicated rows just use this query

delete emp where rowid not in
(select max(rowid) from emp
group by empno having count(empno) >=1)

** here the group by should be on the key field which is supposed to be unique when there is no
duplicates

Report message to a moderator

Re: Is there a way to remove duplicates from a table?(not in a query (distinct) [message #373568 is a reply to message #373515]

Mon, 23 April 2001 17:40

ramesh
Messages: 123
Registered: August 2000

Senior Member

You can find duplicate objects (table also a database object ) from User_objects table .

Report message to a moderator

Previous Topic:	SQL QUERY
Next Topic:	Sequences, Triggers, currval and Transactions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 01 15:54:22 CDT 2024