Re: Duplicate Rows
Herb Blacker wrote:
>
>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 (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?
>I'm using v5.02 on a RS6000 box.
I have used the following query to detect duplicate keys in data before
I actually insert it into my real data. You can adapt this idea - I
unload all but the first row with a given key value. Then I delete
them. The user can look at the unloaded data later and decide what to
do. (My arbitrary assumption is that the first copy is OK.)
Assume key columns are kcol1, kcol2.
unload to tab_dups.unlselect b.*
from my_table a, my_table b
where a.kcol1 = b.kcol1
and a.kcol2 = b.kcol2
and a.rowid < b.rowid -- Guarantee comparing different rows.
;
-- Now get the rowid's of the above duplicated data into a tempt file
--
select b.rowid the_dup -- Give it a display column name
from my_table a, my_table b
where a.kcol1 = b.kcol1
and a.kcol2 = b.kcol2
and a.rowid < b.rowid
into temp dup_rowids
;
delete from my_table where rowid ini (select the_dup from dup_rowids);
I know this is verbose but it saves the data before deleting it.
Too bad you can't put this into a stored procedure; UNLOAD is not a true
SQL command.
--
-- Jake (Never yelled "CROWDED THEATER!" during a fire)
+------------------------------------------------------------+
| The expedient performance of a task with excessive concern |
| regarding its duration-to-completion engenders a virtual |
| certainty of diminished benefit therefrom. |
| -- Benjamin Franklin (but he said it in 3 words) |
+------------------------------------------------------------+