Updating from table with multiple possibilities
От | Worky Workerson |
---|---|
Тема | Updating from table with multiple possibilities |
Дата | |
Msg-id | ce4072df0610031032xf764132gdc731ee941b422bc@mail.gmail.com обсуждение исходный текст |
Список | 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 по дате отправления: