Home » SQL & PL/SQL » SQL & PL/SQL » Delete Duplicate Records using Anaytical function
Delete Duplicate Records using Anaytical function [message #573154] Sat, 22 December 2012 03:31 Go to next message
ramya29p
Messages: 89
Registered: November 2007
Location: Chennai
Member
Hi,
could any one please tell me how to delete duplicated records from a table without using row_id. I found the duplicated rows from a table using Analytical Function. But i could not use the Analytical function in the where condition.

My table(tab2) Structure is
   	DEPTNO	DEPT_NAME	EMPID	SEX	ID1
	10	7jadf	         1	F	1
	40	asdf	         5		5
	10	purchase	 2	M	2
	10	sales	         3	M	3
	30	HR	         4	F	4

I found the Duplicate Record by using the query
 with a as
  (select deptno,dept_name,empid,sex,id1,row_number()over(partition by deptno order by deptno) rnum from tab2)
select * from a where rnum >1


Please tell me how to delete duplicate record .
Thanks in advance

[Updated on: Sat, 22 December 2012 03:35]

Report message to a moderator

Re: Delete Duplicate Records using Anaytical function [message #573155 is a reply to message #573154] Sat, 22 December 2012 03:57 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
What is the primary key of the table? deptno? How do you define duplicate within the context of this problem?
two records having the same deptno? but which one to conserve and eliminate others based on what criterion?


Regards,
Dariyoosh

[Updated on: Sat, 22 December 2012 03:58]

Report message to a moderator

Re: Delete Duplicate Records using Anaytical function [message #573156 is a reply to message #573155] Sat, 22 December 2012 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...In addition

Quote:
(partition by deptno order by deptno)


If you partition by deptno then ALL rows in a partition have the same deptno, and so the ORDER BY you gave is quite silly.

Quote:
could any one please tell me how to delete duplicated records from a table without using row_id.


Why do you want this? Why this restriction? Why can't you use rowid?

Also always post your Oracle version, with 4 decimals.

Regaards
Michel
Re: Delete Duplicate Records using Anaytical function [message #573158 is a reply to message #573156] Sat, 22 December 2012 04:40 Go to previous messageGo to next message
ramya29p
Messages: 89
Registered: November 2007
Location: Chennai
Member
In my table structure, Deptno 10 is getting duplicated. There are 3 rows for Deptno 10. I want to delete the 2 records of Deptno 10 without using rowid
Re: Delete Duplicate Records using Anaytical function [message #573159 is a reply to message #573158] Sat, 22 December 2012 04:54 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
And you don't answer to the question: "What is the criterion according to which the so called duplicate records have to be deleted"? How do you know that one record should be conserved and the other one has to be eliminated? Ok, as you said, there are 3 rows for Deptno = 10, but which has to be deleted and why?
Re: Delete Duplicate Records using Anaytical function [message #573160 is a reply to message #573154] Sat, 22 December 2012 05:31 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
Assuming you don't case which of duplicate deptno rows to keep:

delete tab2
  where 1 < (select row_number()over(partition by deptno order by 1) from tab2)
/


Or without analytics:

delete tab2
  where rowid not in (select min(rowid) from tab2 group by deptno)
/


SY.
Re: Delete Duplicate Records using Anaytical function [message #573161 is a reply to message #573160] Sat, 22 December 2012 07:12 Go to previous messageGo to next message
ramya29p
Messages: 89
Registered: November 2007
Location: Chennai
Member
Hi My criteria is to delete the rows when the deptno column is having duplicate values.
by using the analytical function i found the duplicate rows. i want to delete those duplicate rows

[Updated on: Sat, 22 December 2012 07:12]

Report message to a moderator

Re: Delete Duplicate Records using Anaytical function [message #573162 is a reply to message #573161] Sat, 22 December 2012 07:15 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
Did you read my post???

SY.
Re: Delete Duplicate Records using Anaytical function [message #573163 is a reply to message #573162] Sat, 22 December 2012 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, nor he did read the other ones; otherwise I don't see any reason to not answer our questions.

Regards
Michel
Re: Delete Duplicate Records using Anaytical function [message #573165 is a reply to message #573163] Sat, 22 December 2012 07:46 Go to previous messageGo to next message
ramya29p
Messages: 89
Registered: November 2007
Location: Chennai
Member
Hi Solomon, i tried that Query already. it gives me an error "Single row subquery returns more than one row".
Re: Delete Duplicate Records using Anaytical function [message #573166 is a reply to message #573165] Sat, 22 December 2012 07:58 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
ramya29p wrote on Sat, 22 December 2012 14:46
Hi Solomon, i tried that Query already. it gives me an error "Single row subquery returns more than one row".


Ok, let's take a look to the sample data you provided for deptno = 10

DEPTNO	DEPT_NAME	EMPID	SEX	ID1
10	7jadf	         1	F	1
10	purchase	 2	M	2
10	sales	         3	M	3


Here we have according to your application logic(which you don't explain us) duplicates. Which one should be conserved and which ones should be eliminated and why? (based on what criterion?) Can you explain that with words?


Regards,
Dariyoosh

[Updated on: Sat, 22 December 2012 08:02]

Report message to a moderator

Re: Delete Duplicate Records using Anaytical function [message #573167 is a reply to message #573166] Sat, 22 December 2012 08:29 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
Oops,

I definitely need more coffee:

delete tab2
  where rowid in (
                  select  case
                            when row_number()over(partition by deptno order by 1) > 1 then rowid
                          end
                    from  tab2
                 )
/


But solution I posted without analytics is more efficient.

SY.
EDIT: Oops again. This post should be in reply to message message #573165

[Updated on: Sat, 22 December 2012 08:36]

Report message to a moderator

Re: Delete Duplicate Records using Anaytical function [message #573169 is a reply to message #573167] Sat, 22 December 2012 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But the (stupid) requirement is "without using row_id".
And the actual questions are those that Dariyoosh has repeated several times.
If there is no answer, then we can assume that this either a homework or an interview question and OP failed, anyway.

Regards
Michel

[Edit: missing letter]

[Updated on: Mon, 24 December 2012 02:22]

Report message to a moderator

Re: Delete Duplicate Records using Anaytical function [message #573191 is a reply to message #573169] Sun, 23 December 2012 23:29 Go to previous messageGo to next message
ramya29p
Messages: 89
Registered: November 2007
Location: Chennai
Member
Hi solomon, My criteria is should not use rowid.
Actually this is a interview question.

Re: Delete Duplicate Records using Anaytical function [message #573194 is a reply to message #573191] Mon, 24 December 2012 00:25 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
ramya29p wrote on Sun, 23 December 2012 23:29
Hi solomon, My criteria is should not use rowid.
Actually this is a interview question.



LOL I thought so............ Laughing
Re: Delete Duplicate Records using Anaytical function [message #573202 is a reply to message #573191] Mon, 24 December 2012 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ramya29p wrote on Mon, 24 December 2012 06:29
Hi solomon, My criteria is should not use rowid.
Actually this is a interview question.


And so as I said, you failed.
Until you stop to think that all interview questions have an answer and ask other to answer it and learn by heart all answers you will fail interviews.

Regards
Michel

Re: Delete Duplicate Records using Anaytical function [message #573232 is a reply to message #573191] Mon, 24 December 2012 10:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
ramya29p wrote on Mon, 24 December 2012 00:29
My criteria is should not use rowid.


Then you have to have something that uniquely identifies row, e.g. PK or you could use PL/SQL.

SY.
Re: Delete Duplicate Records using Anaytical function [message #573340 is a reply to message #573232] Wed, 26 December 2012 22:53 Go to previous messageGo to next message
nitinpl
Messages: 1
Registered: December 2012
Location: gurgaon
Junior Member
hi,
after seen your discuusion how will we delete records when there is no pk on table..??is it possible scenario??
Re: Delete Duplicate Records using Anaytical function [message #573341 is a reply to message #573340] Wed, 26 December 2012 23:20 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
>after seen your discuusion how will we delete records when there is no pk on table..??is it possible scenario??

since you choose to not answer any questions we ask you, I choose to not answer any question you post here.
Re: Delete Duplicate Records using Anaytical function [message #573345 is a reply to message #573340] Thu, 27 December 2012 00:17 Go to previous message
flyboy
Messages: 1775
Registered: November 2006
Senior Member
nitinpl wrote on Thu, 27 December 2012 05:53
hi,
after seen your discuusion how will we delete records when there is no pk on table..??is it possible scenario??

Use ROWID. (which original poster refused, so Solomon suggested this as an alternative solution)

@BlackSwan: You are aware that nitinpl is not original poster, aren't you?

[Edit: English]

[Updated on: Thu, 27 December 2012 00:19]

Report message to a moderator

Previous Topic: Trigger fires on deletion of uncommitted data and changes saved
Next Topic: Document contains too many nodes error message select query search all columns for a string
Goto Forum:
  


Current Time: Thu Oct 23 13:14:53 CDT 2014

Total time taken to generate the page: 0.09419 seconds