Thread: matching rows differing only by fkey,pkey
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
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
On Tue, Jun 22, 2004 at 12:34:35 -0400, Matthew Nuzum <matt@followers.net> wrote: > Any ideas? If someone has an alternate method of achieving the same result > I'd be excited to hear about it. A more precise formulation of what exactly you are doing might be helpful. From your description it sounds like you should already know the primary key of the row you are copying and can use currval to get the id of the row just inserted. I get the impression that you are manually using these values instead of having a script do it. But it should be easy to write a script that does this for you.
Thanks for your suggestion to use curval(), that will be useful. I'd entirely forgotten about that function. That likely will help when combined with Richard's suggestion and an idea I already had. I'm sorry that the problem wasn't clearer. The best way I can describe it is like this: The end result is to duplicate the data for a particular record in table "a" so that all of it's related datain 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 <--+ ciddata1 +-->aid +-->biddata2 field1 info1date3 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? 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
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.
Bruno Wolff III wrote: > 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. [snip] > 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. Screams out plpgsql to me - it's good at automating what is basically cut & paste of values. -- Richard Huxton Archonet Ltd