Re: Strange ROWID problem

The select I gave you would show Two of Three duplicate rows as an example. Take off the "and a.rowid <> b.rowid" and you would see all duplicates for a given primary_key. I did the select just so you could see what you would be deleting. My usual statement is

delete from mytable a
 where exists (select 'x'

                 from mytable b
                where a.primary_key = b.primary_key
                  and a.rowid <> b.rowid
               )

Now we could be having two different discussions.

I want to delete all duplicate rows and just leave one row.

You might be asking, "How could I delete "just the second duplicate row" and leave the other duplicate rows. It that case my code does not work.

Lothar Armbrüster (lothar.armbruester_at_rheingau.netsurf.de) wrote:
: 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 |

--
While DSC may claim ownership of all my ideas (on or off the job), DSC does not claim any responsibility for them. Warranty expired when you opened this article and I will not be responsible for its contents or use. Received on Tue Jul 14 1998 - 07:09:24 CDT