Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate rows
icon14.gif  Duplicate rows [message #212190] Thu, 04 January 2007 04:07 Go to next message
manorapps
Messages: 7
Registered: December 2006
Location: Hyderabad
Junior Member

How to delete duplicate rows without using rowid,rownum and without droping the table


regards
manu

[Updated on: Thu, 04 January 2007 04:18]

Report message to a moderator

Re: Duplicate rows [message #212200 is a reply to message #212190] Thu, 04 January 2007 04:20 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Is this on an Oracle database?

MHE

[Updated on: Thu, 04 January 2007 04:20]

Report message to a moderator

Re: Duplicate rows [message #212203 is a reply to message #212190] Thu, 04 January 2007 04:22 Go to previous messageGo to next message
manorapps
Messages: 7
Registered: December 2006
Location: Hyderabad
Junior Member

Yes it is ORACLE

_ _

MANU
Re: Duplicate rows [message #212206 is a reply to message #212203] Thu, 04 January 2007 04:25 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Then I'd like to know why you are reluctant to use ROWID?

MHE
Re: Duplicate rows [message #212208 is a reply to message #212206] Thu, 04 January 2007 04:28 Go to previous messageGo to next message
manorapps
Messages: 7
Registered: December 2006
Location: Hyderabad
Junior Member

Just a bit curious about whether there exists another method or not.

regards
manu
Re: Duplicate rows [message #212217 is a reply to message #212208] Thu, 04 January 2007 04:46 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Without using some sort of DDL I don't think you can. You can without dropping the table: create a second table using CTAS with a SELECT DISTINCT from your table. Then you truncate/delete your table and insert the values from the second table.

MHE
Re: Duplicate rows [message #212231 is a reply to message #212190] Thu, 04 January 2007 05:26 Go to previous messageGo to next message
bsureysh
Messages: 17
Registered: January 2007
Location: india
Junior Member
try using
"having"

it will work..!!
Re: Duplicate rows [message #212233 is a reply to message #212231] Thu, 04 January 2007 05:28 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
bsureysh wrote on Thu, 04 January 2007 12:26
try using
"having"

it will work..!!
Can you show the forum what you mean by that?

MHE
Re: Duplicate rows [message #212235 is a reply to message #212190] Thu, 04 January 2007 05:30 Go to previous messageGo to next message
bsureysh
Messages: 17
Registered: January 2007
Location: india
Junior Member
==========================================
SELECT * FROM EMP WHERE EMPNO IN( SELECT A.EMPNO FROM EMP A, EMP B WHERE A.EMPNO = B.EMPNO GROUP BY A.EMPNO
HAVING COUNT (A.EMPNO) > 1)
==========================================

Re: Duplicate rows [message #212242 is a reply to message #212235] Thu, 04 January 2007 05:36 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That's a select, not a DELETE. As for a select, why don't you use the DISTINCT keyword...

MHE
Re: Duplicate rows [message #212245 is a reply to message #212190] Thu, 04 January 2007 05:50 Go to previous messageGo to next message
bsureysh
Messages: 17
Registered: January 2007
Location: india
Junior Member
see as per the requirement we need to delete the duplicate records :

eg:
SELECT a.loan_nbr FROM temp a
WHERE a.loan_nbr IN (SELECT a.loan_nbr FROM temp a , temp b
WHERE a.loan_nbr = b.loan_nbr
GROUP BY a.loan_nbr
HAVING COUNT(a.loan_nbr) >1)

LOAN_NBR
=========
0624623740
0624623740
0670622331
0670622331
0673185674
0673185674

=========================

DELETE FROM temp a
WHERE a.loan_nbr IN (SELECT a.loan_nbr FROM temp a , temp b
WHERE a.loan_nbr = b.loan_nbr
GROUP BY a.loan_nbr
HAVING COUNT(a.loan_nbr) >1)

6 rows dfeleted

=========================

i think Distinct wont work for this case ..
Re: Duplicate rows [message #212248 is a reply to message #212245] Thu, 04 January 2007 05:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your solution deletes all the rows - the problem is how to delete all but one of the duplicate rows.
Re: Duplicate rows [message #212259 is a reply to message #212190] Thu, 04 January 2007 06:17 Go to previous message
bsureysh
Messages: 17
Registered: January 2007
Location: india
Junior Member
yes.. in that case.. using rowid / rownum needed.
Previous Topic: deleting child records directly
Next Topic: problem in copying
Goto Forum:
  


Current Time: Mon Dec 05 06:59:18 CST 2016

Total time taken to generate the page: 0.11880 seconds