Re: matching rows differing only by fkey,pkey

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.