Re: Strange ROWID problem

Perhaps I'm too late, but I think this is the query that you are looking for:

delete from mytable t
 where not t.rowid = (select min(x.rowid) from mytable x

                               where x.column = t.column);

bill

Lothar Armbrüster <lothar.armbruester_at_rheingau.netsurf.de> wrote in article <978.498T1271T13233926_at_rheingau.netsurf.de>...
> On 11-Jul-98 15:45:37 John Strange wrote:
>
> >Try this
>
> >select a.rowid, b.rowid
> > from mytable a
> > where exists (select 'x'
> > from mytable b
> > where a.primary_key = b.primary_key
> > and a.rowid <> b.rowid
> > )
> >/
>
> Hello John,
>
> this one solves the problem not comletely because it gives *both* of the
> duplicate rows. I just want one!
> Well normally I don't do the select but I do delete. Consider this:
>
> delete my_table where rowid in
> (select a.rowid from
> mytable a, my_table b
> where a.rowid<b.rowid
> and a.primary_key=b.primary_key);
>
> This one delete just one of the duplicates and keeps the second.
> Normally this works, but I have a table where the whole contents is
> deleted because the "a.rowid<b.rowid" doesn't work correctly.
>
> Still any ideas? ;-)
>
> Lothar
>
> --
> Lothar Armbrüster | lothar.armbruester_at_rheingau.netsurf.de
> Schulstr. 12 | lothar.armbruester_at_t-online.de
> D-65375 Oestrich-Winkel |
>
>
Received on Thu Jul 23 1998 - 10:35:46 CDT