OraFAQ Forum: SQL & PL/SQL » Duplicate rows

Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate rows

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Duplicate rows [message #212190]

Thu, 04 January 2007 04:07

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

Maaher
Messages: 7065
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

manorapps
Messages: 7
Registered: December 2006
Location: Hyderabad

Junior Member

Yes it is ORACLE

_ _

MANU

Report message to a moderator

Re: Duplicate rows [message #212206 is a reply to message #212203]

Thu, 04 January 2007 04:25

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Then I'd like to know why you are reluctant to use ROWID?

MHE

Report message to a moderator

Re: Duplicate rows [message #212208 is a reply to message #212206]

Thu, 04 January 2007 04:28

manorapps
Messages: 7
Registered: December 2006
Location: Hyderabad

Junior Member

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

regards
manu

Report message to a moderator

Re: Duplicate rows [message #212217 is a reply to message #212208]

Thu, 04 January 2007 04:46

Maaher
Messages: 7065
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

Report message to a moderator

Re: Duplicate rows [message #212231 is a reply to message #212190]

Thu, 04 January 2007 05:26

bsureysh
Messages: 17
Registered: January 2007
Location: india

Junior Member

try using
"having"

it will work..!!

Report message to a moderator

Re: Duplicate rows [message #212233 is a reply to message #212231]

Thu, 04 January 2007 05:28

Maaher
Messages: 7065
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

Report message to a moderator

Re: Duplicate rows [message #212235 is a reply to message #212190]

Thu, 04 January 2007 05:30

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)
==========================================

Report message to a moderator

Re: Duplicate rows [message #212242 is a reply to message #212235]

Thu, 04 January 2007 05:36

Maaher
Messages: 7065
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

Report message to a moderator

Re: Duplicate rows [message #212245 is a reply to message #212190]

Thu, 04 January 2007 05:50

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 ..

Report message to a moderator