Re: Strange ROWID problem

From: Lothar Armbrüster <lothar.armbruester_at_rheingau.netsurf.de>
Date: 14 Jul 98 20:04:09 +0100
Message-ID: <2167.499T483T12042774@rheingau.netsurf.de>

On 14-Jul-98 13:09:24 John Strange wrote:

>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
> )
>/

Hello John,
you are not quite right. Consider this:

SQLWKS> create table wub (pk char(1));
Anweisung verarbeitet

SQLWKS> insert into wub values ('X');
1 Zeile verarbeitet.

SQLWKS> insert into wub values ('X');
1 Zeile verarbeitet.

SQLWKS> insert into wub values ('X');
1 Zeile verarbeitet.

SQLWKS> commit;
Anweisung verarbeitet

SQLWKS> select a.rowid from wub a
     2> where exists
     3>  (select * from
     4>   wub b
     5>   where a.pk=b.pk
     6>   and a.rowid<>b.rowid);
ROWID             
------------------
00000355.0000.0002

00000355.0001.0002
00000355.0002.0002
3 Zeilen ausgewählt

SQLWKS> delete from wub a
     2> where exists
     3>  (select * from
     4>   wub b
     5>   where a.pk=b.pk
     6>   and a.rowid<>b.rowid);

3 Zeilen verarbeitet

This prooves what I wrote in my previous post. Your delete deletes *all* duplicates *not* leaving one of them in the database.

>Now we could be having two different discussions.

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

Yes that's exactly what I want.

>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.

Well, if I have duplicate rows, I don't care which of them to keep. There is no autumatic way to decide anyway! ;-)

Normally I use a.rowid<b.rowid but in my special case this one fails, which I think is a bug in Oracle. Oracle Support is on it so I think I will get some results soon.

Sincerely,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |

Received on Tue Jul 14 1998 - 14:04:09 CDT