From: Bruno Wolff III <bruno@wolff.to>
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 13:16:49 -0500
Message-ID: <20040622181649.GB20086@wolff.to> (raw)
In-Reply-To: <200406221740.i5MHe6nb015623@ms-smtp-02.tampabay.rr.com>
References: <20040622172704.GA19163@wolff.to>
<200406221740.i5MHe6nb015623@ms-smtp-02.tampabay.rr.com>
On Tue, Jun 22, 2004 at 13:40:03 -0400,
Matthew Nuzum <matt@followers.net> wrote:
> 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.
> Where "b" is the middle table in a many to many relationship. For example,
>
> - a ---- - b ---- - c ----
> aid <--+ bid <--+ cid
> data1 +-->aid +-->bid
> data2 field1 info1
> date3 field2 info2
>
> SELECT a.*,b.*,c.* from a,b,c where b.aid = a.aid and c.bid = b.bid;
>
> So, the goal is to duplicate an object that is made up of the data stored
> across these three tables.
>
> Any suggestions?
First duplicate the record in table a. Its key will be in a sequence
named something like a_aid_seq.
Then for each record in table b with aid equal to the key of the record
being duplicated do the following:
Duplicate the current record in table b. Its aid should be
currval('a_aid_seq'). The new bid will be available in b_bid_seq.
The old bid will need to be noted by the program.
Then you can duplicate all of the records pointing to this record
in table c with something like the following:
INSERT INTO c (bid, info1, info2)
SELECT currval('b_bid_seq'), info1, info2 FROM c WHERE bid = the_old_bid;
Offhand I can't think of a way to avoid using a procedural language to walk
through the b table so that you can easily keep track of which new bid
corresponds to which old bid. However, writing a perl script or plsql
function to do this for you shouldn't be difficult.
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: bruno@wolff.to, matt@followers.net
Subject: Re: matching rows differing only by fkey,pkey
In-Reply-To: <20040622181649.GB20086@wolff.to>
* 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