Updating from table with multiple possibilities - Mailing list pgsql-sql

From Worky Workerson
Subject Updating from table with multiple possibilities
Date
Msg-id ce4072df0610031032xf764132gdc731ee941b422bc@mail.gmail.com
Whole thread Raw
List pgsql-sql
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!


pgsql-sql by date:

Previous
From: Bruno Wolff III
Date:
Subject: Re: Update 3 columns w/ 1 function calc 3 values?
Next
From: paallen@attglobal.net
Date:
Subject: Fw: How to FindNearest