Updating from table with multiple possibilities

From: "Worky Workerson" <worky(dot)workerson(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Updating from table with multiple possibilities
Date: 2006-10-03 17:32:22
Message-ID: ce4072df0610031032xf764132gdc731ee941b422bc@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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!

Browse pgsql-sql by date

  From Date Subject
Next Message paallen 2006-10-03 17:35:55 Fw: How to FindNearest
Previous Message Bruno Wolff III 2006-10-03 17:19:56 Re: Update 3 columns w/ 1 function calc 3 values?