sql - Postgres - 如何在更新子查询中引用外表？

Question

我正在尝试执行以下操作:

UPDATE table1
SET table1.nearest_city_id = subquery.id
FROM 
  (SELECT id FROM cities ORDER BY cities.location <-> table1.location LIMIT 1)
  AS subquery;

即根据空间查询设置表 1 中最近的城市..

但我无法在子查询中引用我正在更新的行。有什么办法解决这个问题吗？

Answer 1

像这样:

UPDATE table1
  SET nearest_city_id = (select id
                         from cities c
                         ORDER BY c.location <-> table1.location
                         LIMIT 1);