From: Rob Sargent <robjsargent@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Duplicates Processing
Date: Fri, 08 Oct 2010 14:07:07 -0600
Message-ID: <4CAF79EB.1080606@gmail.com> (raw)
In-Reply-To: <AANLkTinr=cNsOeMGfOAjg5ncWhQ9p4RbpfyaoV1YN66T@mail.gmail.com>
References: <AANLkTinSF+zqTVHbHp5HgN0TwHX-DjLAwQ4LhE7FYFxZ@mail.gmail.com>
<m3iq1cr0fi.fsf@passepartout.tim-landscheidt.de>
<AANLkTinr=cNsOeMGfOAjg5ncWhQ9p4RbpfyaoV1YN66T@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robjsargent@gmail.com
Subject: Re: Duplicates Processing
In-Reply-To: <4CAF79EB.1080606@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox