Re: SQL Statement

Hi !

Try this:

delete from table_name outer  where rowid > (select min(rowid)

                    from table_name
                   where mail_host   = outer.mail_host)

It will keep the latest entry in the table, deleting all other duplicates.

Regards !!!

Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com

Russell Fray <russ_at_u-net.net> wrote in article <35ed00f3.1105696_at_news.u-net.com>...
>
> 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.
>
>
Received on Wed Sep 02 1998 - 09:10:43 CDT