Re: matching rows differing only by fkey,pkey

On Tue, Jun 22, 2004 at 13:40:03 -0400,
Matthew Nuzum <matt(at)followers(dot)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.