Message-ID: <42492A1D.9060404@archonet.com>
Date: Tue, 29 Mar 2005 11:12:45 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: Andrus Moor <nospameetasoftnospam@online.ee>
Cc: pgsql-sql@postgresql.org
Subject: Re: Merging item codes using referential integrity
References: <d21pqg$25ni$1@news.hub.org>
In-Reply-To: <d21pqg$25ni$1@news.hub.org>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

Andrus Moor wrote:
> 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  );

> BEGIN;
> -- Direct Postgres to update all child tables. This causes error.
> UPDATE parent SET code='1' WHERE code='2';
> -- Remove duplicate row

That's the problem - you can't have a duplicate row at *any* time with a 
primary key. The UNIQUE constraint is instant and can't be deferred (at 
least, not yet).

However, in this case I would simply write a function:

CREATE FUNCTION merge_all(char(10), char(10) AS '
   UPDATE table_1 SET col_1=$2 WHERE col1=$1;
   UPDATE table_2 SET col_2=$2 WHERE col2=$2;
   ...etc...
' LANGUAGE SQL;

Then: SELECT merge_all('OLD_VAL','NEW_VAL') for each value (you could 
even join to your "parent" table if all the values are in there). All 
the updates in the function take place in the same transaction, so if 
there are any problems then all changes will be rolled back.

--
   Richard Huxton
   Archonet Ltd