|
Re: deleteing duplicates using rownum [message #230438 is a reply to message #230428] |
Thu, 12 April 2007 00:18 |
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
|
|
|
|
|
|