Re: Duplicate Rows
In article <64a6bq$gr2@cssun.mathcs.emory.edu>, Herb Blacker
<herbb@jcdcrs4.jobcorps.org> writes
>I've got an old table with all columns allowing nulls (I wasn't here at
>the time!) with a unique index on it (how they got away with it all these
>years I'll never know). The index got corrupted, and rather than trying
>to tbcheck it, I thought it would be faster to drop it and re-create it
tbcheck - Online 5.x so you have rowids!!
>(we're talking 12M rows here).
>Sure enough, it errors out with something like 'cannot insert row with
>duplicate values'.
>My question is: is there a quick way to isolate the duplicate rows
>(perhaps into a temp table) so that I can get rid of them?
Yes - create a duplicate index on the column.
Then do
select <column>, count(*) my_count
from <table>
group by <column>
having count(*) > 1
into temp t1 with no log;
Then
select * from t1
will show you which values in the column are duplicated and how many
times each one occurs.
You can then do
select <table>.<column>, <table>.rowid
from t1,<table>
where t1.<column> = <table>.column>
to get the rowid of each of the affected rows.
Finally you can do
select *
from <table>
where rowid = <rowid value>
to get access to one of the affected rows and only one of the
affected rows. E.g.
delete from <table> where rowid = <rowid-value>
will allow you to delete one of the duplicate rows.
>I'm using v5.02 on a RS6000 box.
>TIA,
>Herb
> -------------------------------------------------------------------------
> Herb Blacker
> Cimarron, Inc.
> Database Administrator - DOL Job Corps San Marcos, Texas
> ------------------------------------------------------------------------
>
--
David Williams