From: Richard Huxton <dev@archonet.com>
To: Matthew Nuzum <matt@followers.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: matching rows differing only by fkey,pkey
Date: Tue, 22 Jun 2004 18:16:16 +0100
Message-ID: <40D86960.4070008@archonet.com> (raw)
In-Reply-To: <200406221634.i5MGYcnb009716@ms-smtp-02.tampabay.rr.com>
References: <200406221634.i5MGYcnb009716@ms-smtp-02.tampabay.rr.com>
Matthew Nuzum wrote:
> When the relationships are one to one or one to many this process is easy,
> however sometimes there's a many to many relationship.
>
> It seems that a helpful tool would be a query that can return just the pkey
> of the original record copied from and the pkey of the newly created record.
>
> For example, if the b table looked like this after a copy of 3 rows:
> bid | aid | field1 | field2 | field3
> 1 | 22 | abc | 123 | abc123
> 2 | 22 | xyz | 456 | xyz456
> 3 | 22 | pdq | 789 | pdq789
> 4 | 23 | abc | 123 | abc123
> 5 | 23 | xyz | 456 | xyz456
> 6 | 23 | pdq | 789 | pdq789
>
> I'd like to get this:
> oldbid | newbid
> 1 | 4
> 2 | 5
> 3 | 6
SELECT
one.bid AS oldbid,
two.bid AS newbid
FROM
b one,
b two
WHERE
one.field1=two.field1 AND ...
AND two.bid > one.bid
;
Of course, if there are 3+ rows with duplicate field1/2/3 then this
won't work.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, matt@followers.net
Subject: Re: matching rows differing only by fkey,pkey
In-Reply-To: <40D86960.4070008@archonet.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