Re: "Seek and destroy" duplicate entries

On 16.10.2006 13:36, Alex wrote:
> Hello NG,
>
> considering the following table-structure (import-table):
>
> Material_Nr || Alternative_Hierarchy
> -------------||--------------------
> A || 10xalpha
> A || 10xalpha
> A || 10xalpha
> A || 10xalpha
> B || 20xgamma
> B || 20xgamma
> B || 20xgamma
> B || 20xgamma
> ...
>
> The data I've to import isn't normalized. Therefore, the original
> datasets are inflated to nearly 140.000.
> While I'm able to delete about 130.000 datasets by killing those, who
> have NULL as "Alternative_Hierarchy", I've still got >8.000 datasets
> left.
>
> But a lot of these are still duplicates!
> Having only the mentioned columns, I failed to apply the method of
> S-A-D using the MIN()/MAX() functions.
>
> Can anyone provide me with a solution of killing the duplicates (except
> one) ?
> Any help will be appreciated!

1. You can use the min(rowid) for every unique combination to delete all superfluous records.

However, I find these approaches more attractive:

2. Use a staging table and move the data from there to the target table that has proper constraints with a SELECT DISTINCT or GROUP BY. (Or is your import table the staging table already?)

3. Filter during import to avoid duplicates during insertion (for example by having SQL*Loader reject records that violate constraints).

Kind regards

        robert Received on Mon Oct 16 2006 - 07:39:34 CDT