OraFAQ Forum: SQL & PL/SQL » how to delete duplicate rows from a table

Home » SQL & PL/SQL » SQL & PL/SQL » how to delete duplicate rows from a table

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

how to delete duplicate rows from a table [message #8671]

Thu, 18 September 2003 02:40

NS Jagan
Messages: 6
Registered: September 2003

Junior Member

how to delete duplicate rows from a table

Report message to a moderator

Re: how to delete duplicate rows from a table [message #8672 is a reply to message #8671]

Thu, 18 September 2003 03:50

Gerald
Messages: 54
Registered: January 2001

Member

Hi dud,
Well got a solution by clonig the table without the duplicateS ... may be there's something better :)

here's how to do:

we have a table with 3 duplicates:
-----------------------------------

create table TOTO (name varchar2(10) value number);
insert into TOTO values ('Tata','1');
insert into TOTO values ('Tata','1');
insert into TOTO values ('Tata','1');
insert into TOTO values ('Titi','2');

We want it without duplicate:
---------------------------------------
so i clone it and do a double drop and reclone:

CREATE TABLE CLO_TOTO AS select distinct * from TOTO;
drop table TOTO;
CREATE TABLE toto AS select distinct * from CLO_TOTO;
drop table CLO_TOTO;

Funny no ?
Bye Take Care
Gerald

Report message to a moderator

Re: how to delete duplicate rows from a table [message #8676 is a reply to message #8671]

Thu, 18 September 2003 05:00

Ani
Messages: 56
Registered: November 2000

Member

delete from tab1 where (field1,field2,......) in
(select field1,field2,...... from tab1 group by
field1,field2,......
having count(*)>1);

Report message to a moderator

Re: how to delete duplicate rows from a table [message #8684 is a reply to message #8671]

Thu, 18 September 2003 08:58

utsav
Messages: 94
Registered: March 2003

Member

Hi

delete from tt t1 where exists (select 'X') from tt t2 where t1.keyvalue=t2.keyvalue and t2.rowid>t1.rowid)

Report message to a moderator

The best solution [message #8696 is a reply to message #8671]

Fri, 19 September 2003 03:21

Mike
Messages: 417
Registered: September 1998

Senior Member

Go to:
http://www.orafaq.net/msgboard/newbies/index1.htm#search

Enter:
delete duplicate

And you will see

Hope that wasn't too hard

Report message to a moderator

Re: how to delete duplicate rows from a table [message #8718 is a reply to message #8671]

Sat, 20 September 2003 07:12

Aravind
Messages: 41
Registered: January 2002

Member

DELETE FROM [[TABLE-NAME]]
WHERE ROWID NOT IN
(SELECT MIN(ROWID) FROM [[TABLE-NAME]]
GROUP BY [[PRIMARY-KEY]])

Report message to a moderator

Previous Topic:	Help for a query......
Next Topic:	request for doubts to be cleared at the earliest.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 00:45:54 CDT 2024