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.