Home » SQL & PL/SQL » SQL & PL/SQL » deleteing duplicates using rownum
deleteing duplicates using rownum [message #230428] Wed, 11 April 2007 23:51 Go to next message
white
Messages: 13
Registered: October 2006
Junior Member
Hi,


How can i delete the duplicate rows by using rownum not with rowid.


Thanks in advance,
Re: deleteing duplicates using rownum [message #230438 is a reply to message #230428] Thu, 12 April 2007 00:18 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

i learnt from one of maheer's reply.
and it is from this thread

and here it is........

Seriously, why would one want to do that, other than not being in an Oracle environment? Don't tell me it's an exam/homework question.

You can, but not in a single statement. Here's what Tom Kyte learns us at his site (asktom.oracle.com):
----------------------------------------------------------------------
in a single statement -- no, relational algebra doesn't permit it. Entirely
duplicate records (where all columns are the same) make this not possible.

using rownum -- no, you cannot use rownum either.

Using rowid, yes, you can use rowid.

Using procedural code, yes, you can use procedural code.

Using more then one statement, yes, I can do this in 3 statements:

insert dups into temp table
delete dups from orig table
put de-duped data back into orig table from temp table

But I came across some solutions, but they're all Oracle specific (using analytic functions). If your interested search the site.




regards,
shanth

[Updated on: Thu, 12 April 2007 00:22]

Report message to a moderator

Re: deleteing duplicates using rownum [message #230443 is a reply to message #230438] Thu, 12 April 2007 00:53 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
not possible with out rowid may be.
Thanks,
srinivas
Re: deleteing duplicates using rownum [message #230965 is a reply to message #230428] Fri, 13 April 2007 10:31 Go to previous messageGo to next message
white
Messages: 13
Registered: October 2006
Junior Member


Hi,

I tried with count(*) with rownum but still i am not getting to delete duplicate rows by using ROWNUM.

So, anyone knows could you please mention the query


Advanced thanks,
Re: deleteing duplicates using rownum [message #231011 is a reply to message #230965] Fri, 13 April 2007 12:39 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
why, why, WHY?
Previous Topic: how to wite this query?
Next Topic: deadlock in table
Goto Forum:
  


Current Time: Thu Dec 12 04:33:24 CST 2024