Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate Rows
Duplicate Rows [message #240963] Mon, 28 May 2007 01:38 Go to next message
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 Go to previous messageGo to next message
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 #240980 is a reply to message #240963] Mon, 28 May 2007 02:16 Go to previous messageGo to next message
pgorama
Messages: 13
Registered: May 2007
Junior Member
No Message Body

[Updated on: Mon, 28 May 2007 02:23]

Report message to a moderator

Re: Duplicate Rows [message #240984 is a reply to message #240980] Mon, 28 May 2007 02:26 Go to previous messageGo to next message
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 #240993 is a reply to message #240984] Mon, 28 May 2007 02:58 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Yup,this is what u have been expecting. Thanks!!!
Re: Duplicate Rows [message #241002 is a reply to message #240993] Mon, 28 May 2007 03:22 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi

caliguardo wrote
Quote:
Yup,this is what u have been expecting. Thanks!!!


i did not expect any thing lol Razz

regards
shanth
Re: Duplicate Rows [message #241016 is a reply to message #241002] Mon, 28 May 2007 03:50 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Well ,I tried it....it dint work for multiple value duplicate entries......
Re: Duplicate Rows [message #241034 is a reply to message #241016] Mon, 28 May 2007 04:11 Go to previous message
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

Previous Topic: parse count=execution with cursor ref
Next Topic: Create Update trigger
Goto Forum:
  


Current Time: Wed Dec 07 18:15:42 CST 2016

Total time taken to generate the page: 0.10965 seconds