Try this make sure the column you use in the join is indexed
To find:
select count(*),indexed_row
from table_a
group by indexed_row
having couny(*) > 1
To delete
delete from table_name a
where a.rowid > (select min(b.rowid) from table_name b
where a.indexed_row=b.indexed_row)
Gjlinker wrote in message =
<19990207132643.13258.00000002_at_ngol03.aol.com>...
>
>Dear all,
>
>>I want to create an unique index but I have som duplicate values. I
>>think I can find them with a combination of count, group and having
>>Count ()> 1 but how can I delete so that only one of the duplicates
>>remains?
>
>Use a SQL statement like this:
>
>delete from emp a
>where exists
>( select 1 from emp b where a.empno = b.empno and a.rowid > b.rowid)
>
>
>Regards Gerrit-Jan Linker,
>
>*************************************************
>*| Gerrit-Jan Linker : gjlinker_at_aol.com
>*|
>*| http://members.aol.com/gjlinker
>*|
>*| Developer of:
>*| Oraxcel, OraWeb, OraSQL, OraCodes and OraDebug
>*************************************************
>LLLL IIII TTTTTTTTTTTT
>LLLL IIII TTTTTTTTTTTT
>LLLL IIII TTTT
>LLLL IIII TTTT
>LLLL IIII TTTT
>LLLL IIII TTTT
>LLLL IIII TTTT
>LLLL IIII TTTT
>LLLL IIII TTTT
>LLLL
>LLLLLLLLLLLLLLLLLLLLLLLLLLLL
>L Linker IT Consulting Ltd L
>LLLLLLLLLLLLLLLLLLLLLLLLLLLL
>
>
>
Received on Sat Feb 13 1999 - 22:47:46 CST
