X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4B485D1B1C8 for ; Tue, 22 Jun 2004 13:34:45 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 52139-01 for ; Tue, 22 Jun 2004 16:34:42 +0000 (GMT) Received: from ms-smtp-02.tampabay.rr.com (ms-smtp-02-smtplb.tampabay.rr.com [65.32.5.132]) by svr1.postgresql.org (Postfix) with ESMTP id A4AE2D1B19C for ; Tue, 22 Jun 2004 13:34:40 -0300 (ADT) Received: from MATTSPC (222-60.26-24.tampabay.rr.com [24.26.60.222]) by ms-smtp-02.tampabay.rr.com (8.12.10/8.12.7) with ESMTP id i5MGYcnb009716 for ; Tue, 22 Jun 2004 12:34:38 -0400 (EDT) Message-Id: <200406221634.i5MGYcnb009716@ms-smtp-02.tampabay.rr.com> From: "Matthew Nuzum" To: Subject: matching rows differing only by fkey,pkey Date: Tue, 22 Jun 2004 12:34:35 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 Thread-Index: AcRYds0gqwQeIUT4QtWFz6Wj2HLM4g== X-Virus-Scanned: Symantec AntiVirus Scan Engine X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.2 tagged_above=0.0 required=5.0 tests=EARN_MONEY, RCVD_IN_SORBS X-Spam-Level: * X-Archive-Number: 200406/206 X-Sequence-Number: 18151 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