Re: Removing duplicates from unique

In article <01bc7061$998685c0$36cc0280@vince.iss.bassinc.com>, "Vince Pachiano" <pachiano@dayton.bassinc.com> wrote: >Somehow some duplicate data "sneaked" into my database >that should have a unique index constraint. Well, I have plugged the >leaks, but now I am stuck with a database that I cannot re-index >due to the duplicate data. > >How can I easily (AUTOMATED) remove the duplicate data? > >I can probably write some UNIX scripts to sort/sort -u, etc., but >I was hoping there was a better way Its pretty simple if you have OL7 (I'm not sure of the other versions). OL7 provides the facility to fiddle with the mode of your index. Along with the the violations & diagnostic tables, this can be a very safe way of fixing your data. What you'd have to do goes something like this. 1. Unload your dup_table. 2. Delete all rows from the table (Drop & recreate may be faster, but caution for referential constraints!). 3. create your unique index. 4. SET INDEXES unique_index FILTERING (Change the mode of your index). 4. START VIOLATIONS TABLE FOR dup_table. 5. Load your table. At the end, you will find your dup_table containing only 'valid' rows, while your violations table will have all the 'bad' rows. BTW, the violations table contains every single column of the original table plus a few more. If your dup rows are truly dup rows and not just dup on index columns, your task ends. If only the index columns are duplicate, while the rest of the columns differ, you will still have to decide whether the row in the violations table is 'bad' or the row in the original table is 'bad' (but got in because it was first in line while loading) and take appropriate action. When all is OK, set your index mode to enabled (SET INDEXES unique_index ENABLED), STOP VIOLATIONS TABLE FOR dup_table, and then drop the violations tables (dup_table_vio and dup_table_dia are the default names). HTH. ----------------------- Rudy Fernandes GIC, Kuwait OL 7.20UC4, 4GL 6.04UC1 -----------------------