Home » SQL & PL/SQL » SQL & PL/SQL » delete duplicate rows
delete duplicate rows [message #220876] Thu, 22 February 2007 10:04 Go to next message
pramodgarre
Messages: 5
Registered: February 2007
Junior Member
this is simple yet complicate query....
i hav some duplicate rows ...i hav to keep one n delete all repeated rows

for ex.....if i hav table as follows....

1 pramod
2 xxxxx
3 xxxxx
1 pramod
1 pramod


my 0/p should be(just using single query).....
1 pramod
2 xxxxx
3 xxxxx

how can i do tht.....thx in advance

Re: delete duplicate rows [message #220877 is a reply to message #220876] Thu, 22 February 2007 10:07 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Perhapse you should try to do a search on the forum for
"delete duplicate rows" before you ask.

There are plenty of solutions for what you are asking
Re: delete duplicate rows [message #220878 is a reply to message #220876] Thu, 22 February 2007 10:07 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Search the board and you will find anything you need!
Re: delete duplicate rows [message #220879 is a reply to message #220878] Thu, 22 February 2007 10:10 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

gbarbisan wrote on Thu, 22 February 2007 17:07
Search the board and you will find anything you need!


Anything I need, now that's impressive !
Razz
Re: delete duplicate rows [message #220901 is a reply to message #220876] Thu, 22 February 2007 12:03 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
pramodgarre wrote on Thu, 22 February 2007 11:04
this is simple yet complicate query....



Is that possible? It's cold as hell here right now. But it's time for lunch, so I'm off to get some jumbo shrimp. Unfortunately, it got freezer burn, so I had to junk it. It was looking pretty ugly.
Re: delete duplicate rows [message #220999 is a reply to message #220901] Fri, 23 February 2007 02:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And normally they look so pretty......


But, returning briefly to the Op's question: If you just want to remove duplicate rows from the query results, change your SELECT <column list> to SELECT DISTINCT <column list>
Re: delete duplicate rows [message #221210 is a reply to message #220876] Sun, 25 February 2007 08:02 Go to previous messageGo to next message
vamshi
Messages: 6
Registered: November 2000
Location: Hyderabad
Junior Member

Hello Friend,

Deleting Duplicate Rows from a table is..

SQL Query:

First insert records with duplicate values and try out this query.

DELETE FROM TABLE_NAME A WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM TABLE_NAME WHERE COLUMN_1=A.COLUMN_1);

Note: Records would be deleted from the first the latest would exist.
Re: delete duplicate rows [message #221220 is a reply to message #221210] Sun, 25 February 2007 12:27 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
vamshi wrote on Sun, 25 February 2007 09:02
Hello DELETE FROM TABLE_NAME A WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM TABLE_NAME WHERE COLUMN_1=A.COLUMN_1);

Note: Records would be deleted from the first the latest would exist.


Sorry, that is not accurate. Why do you think the MAX(ROWID) is the most recent row? That is simply not true.
Re: delete duplicate rows [message #221337 is a reply to message #221220] Mon, 26 February 2007 06:49 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
hi
this query prefect working
DELETE FROM TABLE_NAME WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM TABLE_NAME group by ID);
Thanks,
srinivas
Re: delete duplicate rows [message #221476 is a reply to message #221337] Tue, 27 February 2007 01:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It will work in that it will delete all but one row.
What it won't do is what @Vamshi claimed, and that is keep the most recent row added.
Re: delete duplicate rows [message #254432 is a reply to message #220876] Thu, 26 July 2007 13:53 Go to previous messageGo to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
thank you for reply.

but If i want to retrieve how many duplicate values are there in table.How can we write
Re: delete duplicate rows [message #254434 is a reply to message #254432] Thu, 26 July 2007 13:57 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Open a new topic with this new question.
./fa/448/0/
Regards
Michel
Previous Topic: how to pass column name and table name as arguments to a function
Next Topic: packages.
Goto Forum:
  


Current Time: Sun Dec 04 18:51:00 CST 2016

Total time taken to generate the page: 0.13287 seconds