我试图在Postgres 9.1.3中使用此查询:
WITH stops AS (
SELECT citation_id,
rank() OVER (ORDER BY offense_timestamp,
defendant_dl,
offense_street_number,
offense_street_name) AS stop
FROM consistent.master
WHERE citing_jurisdiction=1
)
UPDATE consistent.master
SET arrest_id = stops.stop
WHERE citing_jurisdiction=1
AND stops.citation_id = consistent.master.citation_id;
我收到此错误:
ERROR: missing FROM-clause entry for table "stops"
LINE 12: SET arrest_id = stops.stop
^
********** Error **********
ERROR: missing FROM-clause entry for table "stops"
SQL state: 42P01
Character: 280
我真的很困惑根据Postgres文档,WITH子句显示正确。如果我在WITH子句中单独运行查询,则会得到正确的结果。
最佳答案
从fine manual:
因此,您只需要一个FROM子句:
WITH stops AS (
-- ...
)
UPDATE consistent.master
SET arrest_id = stops.stop
FROM stops -- <----------------------------- You missed this
WHERE citing_jurisdiction=1
AND stops.citation_id = consistent.master.citation_id;
错误消息甚至说了很多:
关于sql - 使用WITH子句查询时出现Postgres “missing FROM-clause entry”错误,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/9643859/