From: Matthew Nuzum <matt@followers.net>
To: pgsql-sql@postgresql.org
Subject: matching rows differing only by fkey,pkey
Date: Tue, 22 Jun 2004 12:34:35 -0400
Message-ID: <200406221634.i5MGYcnb009716@ms-smtp-02.tampabay.rr.com> (raw)
I'm duplicating some fields in the table but the duplicates will have a new
primary key and a new foreign key. For example,
Table "b" looks like this:
bid (pkey default value is a sequence)
aid (fkey)
field1
field2
field3
INSERT INTO b (aid, field1, field2, field3)
SELECT 23, field1, field2, field3 from b where aid = 22;
"b" is the middle table of a many to many relationship. The end result is
to duplicate the data for a particular record in table "a" so that all of
it's related data in tables "b" and "c" is duplicated.
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
Any ideas? If someone has an alternate method of achieving the same result
I'd be excited to hear about it.
Matthew Nuzum | ISPs: Make $200 - $5,000 per referral by
www.followers.net | recomending Elite CMS to your customers!
matt@followers.net | http://www.followers.net/isp
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: matt@followers.net
Subject: Re: matching rows differing only by fkey,pkey
In-Reply-To: <200406221634.i5MGYcnb009716@ms-smtp-02.tampabay.rr.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