Re: "Seek and destroy" duplicate entries

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!
>
>
> Alex Sauer
>

Depending on how many rows, you may find that the MIN(ROWID) option previously discussed is slow. Since you have no FK constraints on this table, you might want to try this approach:

CREATE TABLE import_table_temp
AS SELECT DISTINCT material_nr, alternative_hierarchy

    FROM import_table;

TRUNCATE import_table;

INSERT INTO import_table SELECT material_nr, alternative_hierarchy

    FROM import_table_temp;

DROP TABLE import_table_temp;

This is just an alternative solution. And there is no guarantee that this is faster.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown