pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Andrus Moor <nospameetasoftnospam@online.ee>
To: pgsql-sql@postgresql.org
Subject: Merging item codes using referential integrity
Date: Fri, 25 Mar 2005 21:46:11 +0200
Message-ID: <d21pqg$25ni$1@news.hub.org> (raw)

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. 





view thread (3+ messages)  latest in thread

Message-ID: <d21pqg$25ni$1@news.hub.org>
Permalink:  ../d21pqg$25ni$1@news.hub.org/
Also on:    postgresql.org/message-id/d21pqg$25ni$1@news.hub.org

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: nospameetasoftnospam@online.ee
  Subject: Re: Merging item codes using referential integrity
  In-Reply-To: <d21pqg$25ni$1@news.hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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