This is true. Thank's.
You can find double keys with 'having count(*)', but it is impossible to
delete them. (incorrect syntax...)
Following exaample is possible to execute:

-- Find double Keys:
select department_id, count(*) from dep2
group by department_id
having count(*) > 1;

-- delete double key's
delete from dep2 dp1
where rowid > (select MIN(rowid) from dep2 dp2
where dp1.department_id = dp2.department_id);


:-))
Vera

-----Ursprüngliche Nachricht-----
Von: Lloyd Ruskin
[mailto:oracledba-ezmlmshield-x40544413.[Email address protected]
Gesendet: Freitag, 29. Juli 2005 11:33
An: LazyDBA Discussion
Betreff: RE: Deleting duplicate rows !!!

I think this will delete both rows.

You need to use the pseudo column RWID and delete where ROWID>1

Lloyd


-----Original Message-----
From: vera mitaroff [mailto:oracledba-ezmlmshield-x74660649.[Email address
protected]
Sent: 29 July 2005 10:28
To: LazyDBA Discussion
Subject: WG: Deleting duplicate rows !!!

You can find ( and delete) this rows with following queriy:
'Pos_sendungsnr' ist the unique - Key in this example:

-- Control:
select pos_sendungsnr from eu_faktura_header group by pos_sendungsnr having
count(*) > 1

-- Delete:
Delete from eu_faktura_header
group by pos_sendungsnr
having count(*) > 1

Good luck!
Vera

-----Ursprüngliche Nachricht-----
Von: VGaddipati
[mailto:oracledba-ezmlmshield-x12795000.[Email address protected]
Gesendet: Freitag, 29. Juli 2005 11:09
An: LazyDBA Discussion
Betreff: Deleting duplicate rows !!!

Hi folks,

we have a huge table with 3 million rows. After an import (disabling the
constraints on the table) we ended up having 4.5 million rows with lots of
duplicate rows.

Is there any way to delete these duplicate rows from the table ?




Kind Regards,
Venugopal R.
Technical Architect (Database).


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com To unsubscribe: see
http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com By using this list you agree to
these terms:http://www.lazydba.com/legal.html





--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com To unsubscribe: see
http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com By using this list you agree to
these terms:http://www.lazydba.com/legal.html



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html



Oracle LazyDBA home page