如何從 UPDATE 子查詢函式中訪問行值？

我需要這樣使用UPDATE：

update offer set (bank, bank_id) = (cost, gid)
from (
select cost, osm_points.gid from pgr_kdijkstraCost(
 'SELECT gid as id, source, target, walk_cost as cost FROM ways',
 offer.vertice_id,
 array(select vertice_id from osm_points where super_type = 'bank'),
 false,false)
join osm_points on id2 = vertice_id
where cost != -1
and osm_points.super_type = 'bank'
order by cost
limit 1)t ;

但我得到以下資訊：

錯誤：對錶“offer”的 FROM 子句條目的無效引用

提示：表“offer”有一個條目，但不能從這部分查詢中引用它。

先感謝您

經過一些Google搜尋和@Daniel 的建議後，我得出了以下解決方案，我需要定義自己的類型並使用它從子查詢中提取數據：

create type closest_poi as (cost float, poi_gid bigint);
with closest as (
select
 distinct o.gid,
 ((select row(cost,gid)
 from pgr_kdijkstraCost(
   'SELECT gid as id, source, target, walk_cost as cost FROM ways',
   o.vertice_id,
   array(select vertice_id
         from osm_points
         where super_type = 'kindergarden'),
   false,false)
 join osm_points on id2 = vertice_id
 where cost != -1
 and super_type = 'kindergarden'
 order by cost
 limit 1)::text::closest_poi).*
from offer o, osm_points p
where p.super_type = 'kindergarden'
and st_dwithin(o.the_geom::geography,p.the_geom::geography,5000)
order by 1)

update offer 
set 
 kindergarden = closest.cost,
 kindergarden_id = closest.poi_gid
from closest
where offer.gid = closest.gid;

當使用這種形式的查詢時：UPDATE a SET columns=values FROM b WHERE ...，b子查詢獨立於 進行評估a，並且查詢末尾的 WHERE 塊應該連接 和 的a行b。

問題中的查詢嘗試在子查詢內部而不是在最上層的 WHERE 子句中加入（實際上它沒有這樣的 WHERE 子句），但這是不允許的，這就是錯誤消息所指示的。

使用相關子查詢更新每一行的 SQL 標準方法是：

UPDATE a SET (col1,col2) =
  (SELECT val1,val2 FROM b WHERE expression-needing-values-from-a)

但是在舊版本的 PostgreSQL（9.4 或更早版本）中，手冊明確指出這種形式沒有實現。從 9.5 開始，它被實施。

用 postgres 9.4 模擬它的典型方法是a在子查詢中使用表的副本，並在上層的 WHERE 子句中連接回更新的表，如下所示：

UPDATE a SET (col1,col2)=(s.col1,s.col2)
 FROM (SELECT a2.ctid,col1,col2 AS ctid_of_row FROM b, a AS a2 WHERE...) AS s
  WHERE s.ctid_of_row = a.ctid;

知道這ctid是指示行版本在其表中的物理位置的系統偽列。或者，如果有主鍵，則可以使用主鍵。

在您的情況下，轉換並不明顯，因為您的子查詢旨在僅生成一行，其ORDER BY... LIMIT 1末尾。應該重新設計它以在單個結果集中生成所有目標行，大概是(verticeid,cost,gid)wherecost和gidare 確定 per offer.verticeid。

引用自：https://dba.stackexchange.com/questions/102904