我试图在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子句中单独运行查询,则会得到正确的结果。
从精美的手册中:
有两种方法可以使用数据库中其他表中包含的信息来修改表:使用子选择,或在FROM子句中指定其他表。
FROM
因此,您只需要一个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;
错误消息甚至说了很多:
错误:缺少表“ stops”的FROM子句条目