| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Statement
Try this but test it first, i've only tried it once
but it seems to work ok.
delete from Table a where rowid !=
(select min(rowid)
from
Table b where b.mail_host = a.mail_host);
Regards
Gary Smith
Russell Fray wrote:
> Hi, thanks to some very helpful people in this group I now have the
> following statement which lists me all occurences of 'mail_host' which
> are duplicated within 'Table'.
>
> select mail_host, count(*) from Table
> having count(*) > 1 group by mail_host;
>
> What I now need to do is incorporate this clause in to a delete
> statement, which deletes all-but-one of the mail_host's which are
> duplicated.
>
> ie, if there are 5 entries in mail_host of 'smith', then I want to
> delete 4 of them and leave 1, hence removing all duplicates.
>
> Please cc replies to russ_at_u-net.net if possible.
>
> Thanks in anticipation,
> Russell Fray.
--
 |
 |