DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=NbS0ejY8DHgIX68OrvLjrRXicUnsLVURRHmKIpGiEVSfdtwuA79mhxBAC79jNymmhrKTWTD9DWllpP8QbOYU8Qc/IsHK6nKeUJTym1U1yDrXKztuzdNnRfVMdU07gB3RVaLvqwU1wA91cGLNxYsxW8/WnI2gM+JbjCFnT32l54k=
Message-ID: <ce4072df0610031032xf764132gdc731ee941b422bc@mail.gmail.com>
Date: Tue, 3 Oct 2006 13:32:22 -0400
From: "Worky Workerson" <worky.workerson@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Updating from table with multiple possibilities
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I have two tables, ips and ranges, where ips contains a list of ips
(INET) and ranges contains a list of ip blocks (CIDR), ala:

CREATE TABLE ips (ip INET, info varchar);
CREATE TABLE ranges (range CIDR, info varchar);

I would like to update the info column in ips to be the info column of
the most specific block in ranges.  For example, if ranges had the
following data:

0.0.0.0/0, 'top level class'
18.0.0.0/8, 'MIT'
18.228.0.0/20, 'MIT Group B'

and ips only had the row "'18.228.0.1', NULL" then doing an update
would change the column in ips to "'18.228.0.1', 'MIT Group B'"

I can't quite figure out the update syntax to get this to work.  This
obviously does not work (as mentioned in the manual), as there are
multiple results returned from the 'join' and I only want the 'best'
match:

UPDATE ips SET info = ranges.info FROM ranges WHERE ip <<= range

I figure that I have to use subselects, but I can't seem to figure out
how to get the data out of the subselect (i.e. the info column).  Do I
need to join ips and ranges explicitly?

Thanks!