Mime-Version: 1.0 (Apple Message framework v622)
Content-Transfer-Encoding: 7bit
Message-Id: <ffaabb4676ab075beb4b6d041ec280c4@Chaos1.DE>
Content-Type: text/plain; charset=US-ASCII; format=flowed
To: pgsql-sql@postgresql.org
From: Axel Rau <Axel.Rau@Chaos1.DE>
Subject: Updating cidr column with network operator
Date: Thu, 22 Sep 2005 12:52:16 +0200

Hi SQLers,

I have a fk from address to network and try to update the foreign key 
column to point at the network, "it belongs to":

CREATE TABLE network (
   id      cidr     PRIMARY KEY     , -- 'PK, ,IPv4/6 Network address'
)

CREATE TABLE address (
   id		 inet     PRIMARY KEY	  , -- 'PK of IPv4/6 host address'
   network cidr     NOT NULL		    -- 'FK to Network table'
                       REFERENCES network ON DELETE CASCADE ON UPDATE 
CASCADE
)

I tried (using the WHERE clause to eliminate the addresses were no 
corresponding net exists):
UPDATE address
	SET network = (SELECT N.id WHERE A.id << N.id)
	FROM address A, network N
	WHERE A.id << N.id;

But this ended up with all network columns pointing at the same net 
(-:).
Any help would be appreciated.

Axel
Axel Rau, Frankfurt, Germany                           +49-69-951418-0