misunderstanding UPDATE FROM syntax

Using PostGIS I have two tables, the first one contains the boundary of 250 cities and the second one contains the coundary of all country in the world.

I am trying to affect to each cities the country it belongs to. The following query allow me to get the result I desire.

SELECT DISTINCT ON (cities.id) cities.id, country.id
FROM cities
LEFT JOIN country ON st_intersects(country.geom, cities.geom)

But when I am using this query :

UPDATE cities
SET country_id=subq.id
FROM (SELECT DISTINCT ON (cities.id) country.id
    FROM cities
    LEFT JOIN country ON st_intersects(country.geom, cities.geom)) AS subq

The country_id column is full of the same number.

What did I miss in the using of the UPDATE FROM syntax ?

Solution

You need to relate the two queries with a join condition:

UPDATE cities
  SET country_id=subq.id
FROM (
  SELECT DISTINCT ON (cities.id) country.id
  FROM cities
    LEFT JOIN country ON st_intersects(country.geom, cities.geom)
) AS subq
  where subq.id = cities.id;

But I think you don't need the sub-select to begin with. You can join the country table directly to the cities table:

UPDATE cities
  SET country_id = country.id
FROM country 
where st_intersects(country.geom, cities.geom)