From: Worky Workerson <worky.workerson@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Updating from table with multiple possibilities
Date: Tue, 3 Oct 2006 13:32:22 -0400
Message-ID: <ce4072df0610031032xf764132gdc731ee941b422bc@mail.gmail.com> (raw)
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: worky.workerson@gmail.com
Subject: Re: Updating from table with multiple possibilities
In-Reply-To: <ce4072df0610031032xf764132gdc731ee941b422bc@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox