Re: Duplicate Rows
You can try this SQL also to detect duplicate rows.
Assuming that kcol1 and kcol2 are the keys in the table1, try the
SQL below:
select kcol1, kcol2, count(*) from table1 group by kcol1, kcol2
having count(*) > 1
This should display all rows which have duplicate rows based on the keys.
Hope this helps,
Uday
----
} 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.unl
} select 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) |
} +------------------------------------------------------------+
--
Uday Shankar
Financial Products Group
Republic National Bank of New York
uday@dpg.rnb.com
(212) 525-8000