Re: Duplicates Processing

On 10/08/2010 01:42 PM, Gary Chambers wrote:
> Tim,
> 
> Thanks for taking the time to reply!
> 
>> | INSERT INTO substitutes ([...])
>> |   SELECT [...] FROM
>> |     (SELECT *,
>> |             ROW_NUMBER() OVER (PARTITION BY wattage, tolerance, temperature
>> |                         ORDER BY part_number) AS RN
>> |      FROM parts) AS SubQuery
>> |   WHERE RN > 1;
> 
>> | DELETE FROM parts
>> | WHERE primary_key IN
>> |   (SELECT primary_key FROM
>> |     (SELECT *,
>> |             ROW_NUMBER() OVER (PARTITION BY wattage, tolerance, temperature
>> |                                ORDER BY part_number) AS RN
>> |      FROM parts) AS SubQuery
>> |    WHERE RN > 1);
> 
> You have solved the problem precisely as I described it.  In my haste
> to make the request for assistance, I omitted one critical piece of
> information that may call into question my data model.  In its current
> state, my substitute parts table contains only the part number (the
> "new" one, so-to-speak), a foreign key reference to the original parts
> table, and some location data (which is also in the original parts
> table).  Is there any advice you can offer in light of what I have
> just described? I apologize for the oversight.
> 
> -- Gary Chambers
> 

Perhaps a trade off between nullable fields and redundant types.  If
your original table simply had a nullable column called
isReplacementFor, into which you place in the subsequent rows the id of
the first instance found.