Postgres“缺少FROM子句条目”使用WITH子句查询时出错 [英] Postgres "missing FROM-clause entry" error on query with WITH clause

问题描述

我正尝试在Postgres 9.1.3中使用此查询：

I am trying to use this query in 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子句中单独运行查询，则会得到正确的结果。

I'm really confused. The WITH clause appears correct per Postgres documentation. If I separately run the query in the WITH clause, I get correct results.

推荐答案

从高级手册：

有两种方法可以使用数据库中其他表中包含的信息来修改表：使用子选择，或在 FROM 子句中指定其他表。

There are two ways to modify a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the FROM clause.

因此，您只需要一个FROM子句：

So you just need a FROM clause:

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;

错误消息甚至说得太多：

The error message even says as much:

错误：表 stops缺少FROM子句条目

ERROR: missing FROM-clause entry for table "stops"

这篇关于Postgres“缺少FROM子句条目”使用WITH子句查询时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！