SQL 使用 WITH 子句查询时 Postgres“缺少 FROM 子句条目”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9643859/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres "missing FROM-clause entry" error on query with WITH clause
提问by Aren Cambre
I am trying to use this query in Postgres 9.1.3:
我试图在 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;
I get this error:
我收到此错误：
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
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.
我真的很困惑。根据 Postgres 文档，WITH 子句似乎是正确的。如果我在 WITH 子句中单独运行查询，我会得到正确的结果。
回答by mu is too short
From the fine manual:
来自精美手册：
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
FROMclause.
有两种方法可以使用数据库中其他表中包含的信息修改表：使用子选择，或在
FROM子句中指定附加表。
So you just need a FROM clause:
所以你只需要一个 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;
The error message even says as much:
错误消息甚至说：
ERROR: missing FROM-clause entry for table "stops"
错误：缺少表“停止”的 FROM 子句条目
回答by Abdellah Alaoui
This can also happen if you mistype a table name. For example:
如果您输入错误的表名，也会发生这种情况。例如：
UPDATE profiles SET name = ( profile.first_name ) WHERE id = 1
Instead of profilesi incorrectly used profile!! This would work:
而不是profiles我错误地使用了profile！！这会起作用：
UPDATE profiles SET name = ( profiles.first_name ) WHERE id = 1
