Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Statement
On Wed, 02 Sep 1998 08:28:49 GMT, russ_at_u-net.net (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.
>
delete from T a
where exists ( select null
from T b where a.mail_host = b.mail_host having count(mail_host) > 1 group by mail_host ) and rowid <> ( select min(rowid) from T b where a.mail_host = b.mail_host )/
Will do the trick.
chris.
>Please cc replies to russ_at_u-net.net if possible.
>
>Thanks in anticipation,
>Russell Fray.
Received on Wed Sep 02 1998 - 09:22:13 CDT