Duplicate Rows [message #240963] |
Mon, 28 May 2007 01:38  |
caliguardo
Messages: 107 Registered: February 2007 Location: Chennai
|
Senior Member |

|
|
Is there a way to delete all the duplicate rows without using rowid? This should be achieved using a single query. Not by using another table ...
|
|
|
Re: Duplicate Rows [message #240978 is a reply to message #240963] |
Mon, 28 May 2007 02:13   |
shanthkumaar
Messages: 156 Registered: February 2007 Location: india,chennai
|
Senior Member |

|
|
hi,
this question has been asked many times and this was the reply given mostly
Quote: | 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.
|
and this reply is from this thread
regards
shanth
|
|
|
|
Re: Duplicate Rows [message #240984 is a reply to message #240980] |
Mon, 28 May 2007 02:26   |
shanthkumaar
Messages: 156 Registered: February 2007 Location: india,chennai
|
Senior Member |

|
|
hi,
@pgorama>>> i guess OP wants to delete all the duplicate rows. with out using row id, in a single query.. some thing like this..
delete from emps e
where emp_no in(select emp_no from emps d
where d.ename=e.ename
minus
select emp_no from emps f
where f.ename=e.ename
and rownum=1)
regards
shanth
[Updated on: Mon, 28 May 2007 02:27] Report message to a moderator
|
|
|
|
|
|
Re: Duplicate Rows [message #241034 is a reply to message #241016] |
Mon, 28 May 2007 04:11  |
shanthkumaar
Messages: 156 Registered: February 2007 Location: india,chennai
|
Senior Member |

|
|
hi,
what do you mean by multiple value duplicate entries?
is that something like this
select * from emp
EMP_NO ENAME DEPT_NO SALARY BONUS HRA DATE_OF_JOINING
1 shanth 1 10000 4000 600 05-JUN-06
2 kumara 2 11000 4100 610 06-JUN-06
3 john 2 10500 4500 600 07-JUN-06
4 prem 3 9000 3500 500 05-AUG-06
6 priya 3 15000 8000 900 05-JUN-03
7 ravi 4 16000 9000 900 05-JUN-01
8 ravi 4 16000 9000 900 05-JUN-01
9 ravi 4 16000 9000 900 05-JUN-01
delete from emp e
where emp_no in(select emp_no from emp d
where d.ename=e.ename
minus
select emp_no from emp f
where f.ename=e.ename
and rownum=1)
2 row(s) deleted.
select * from emp
EMP_NO ENAME DEPT_NO SALARY BONUS HRA DATE_OF_JOINING
1 shanth 1 10000 4000 600 05-JUN-06
2 kumara 2 11000 4100 610 06-JUN-06
3 john 2 10500 4500 600 07-JUN-06
4 prem 3 9000 3500 500 05-AUG-06
6 priya 3 15000 8000 900 05-JUN-03
7 ravi 4 16000 9000 900 05-JUN-01
regards
shanth
[Updated on: Mon, 28 May 2007 04:12] Report message to a moderator
|
|
|