From: "Andrus Moor" <nospameetasoftnospam@online.ee>
Subject: Merging item codes using referential integrity
Date: Fri, 25 Mar 2005 21:46:11 +0200
Organization: Hub.Org Networking Services
Lines: 40
Message-ID: <d21pqg$25ni$1@news.hub.org>
To: pgsql-sql@postgresql.org

I have item table and a lot of child tables where the items are used.
I want to merge two item codes into single item in all tables.
It is not nice to write a lot of separate UPDATE statements for each table.
So I want to utilize REFERENCES clause for merging.

I tried the following code but got duplicate key error in UPDATE
statement.

Any idea how to impement this?

CREATE TABLE parent ( code CHAR(10) PRIMARY KEY  );

CREATE TABLE orders ( anyfield CHAR(10) REFERENCES parent ON UPDATE 
CASCADE );
CREATE TABLE invoices ( anyotherfield CHAR(10) REFERENCES parent ON UPDATE 
CASCADE );
-- ... a lot of more child tables with different table and field names 
but -- always with same REFERENCES clause.

INSERT INTO parent VALUES ('1');
INSERT INTO parent VALUES ('2');
INSERT INTO orders VALUES ('1');
INSERT INTO invoices VALUES ('1');
INSERT INTO orders VALUES ('2');
INSERT INTO invoices VALUES ('2');

BEGIN;
-- Direct Postgres to update all child tables. This causes error.
UPDATE parent SET code='1' WHERE code='2';
-- Remove duplicate row
CREATE TABLE parent AS
  SELECT * FROM parent
  GROUP BY CODE ;
COMMIT;


Andrus.