pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmatching rows differing only by fkey,pkey
6+ messages / 3 participants
[nested] [flat]
* matching rows differing only by fkey,pkey
@ 2004-06-22 16:34 Matthew Nuzum <matt@followers.net>
0 siblings, 2 replies; 6+ messages in thread
From: Matthew Nuzum @ 2004-06-22 16:34 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: matching rows differing only by fkey,pkey
@ 2004-06-22 17:16 Richard Huxton <dev@archonet.com>
parent: Matthew Nuzum <matt@followers.net>
1 sibling, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2004-06-22 17:16 UTC (permalink / raw)
To: Matthew Nuzum <matt@followers.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: matching rows differing only by fkey,pkey
@ 2004-06-22 17:27 Bruno Wolff III <bruno@wolff.to>
parent: Matthew Nuzum <matt@followers.net>
1 sibling, 1 reply; 6+ messages in thread
From: Bruno Wolff III @ 2004-06-22 17:27 UTC (permalink / raw)
To: Matthew Nuzum <matt@followers.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: matching rows differing only by fkey,pkey
@ 2004-06-22 17:40 Matthew Nuzum <matt@followers.net>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 6+ messages in thread
From: Matthew Nuzum @ 2004-06-22 17:40 UTC (permalink / raw)
To: 'Bruno Wolff III' <bruno@wolff.to>; +Cc: pgsql-sql
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 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?
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: matching rows differing only by fkey,pkey
@ 2004-06-22 18:16 Bruno Wolff III <bruno@wolff.to>
parent: Matthew Nuzum <matt@followers.net>
0 siblings, 1 reply; 6+ messages in thread
From: Bruno Wolff III @ 2004-06-22 18:16 UTC (permalink / raw)
To: Matthew Nuzum <matt@followers.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: matching rows differing only by fkey,pkey
@ 2004-06-22 19:47 Richard Huxton <dev@archonet.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2004-06-22 19:47 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Matthew Nuzum <matt@followers.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-06-22 19:47 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-22 16:34 matching rows differing only by fkey,pkey Matthew Nuzum <matt@followers.net>
2004-06-22 17:16 ` Richard Huxton <dev@archonet.com>
2004-06-22 17:27 ` Bruno Wolff III <bruno@wolff.to>
2004-06-22 17:40 ` Matthew Nuzum <matt@followers.net>
2004-06-22 18:16 ` Bruno Wolff III <bruno@wolff.to>
2004-06-22 19:47 ` Richard Huxton <dev@archonet.com>
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