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