Re: Removing duplicates from unique
Another easy way is:
1. Unload your data
2. Drop and recreate your table with the index as unique
3. Use dbload to load your data
All your duplicates will be eliminated and left in a log file
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
-----------------------