PostgreSQL：如何在CTE中更新行

Question

我正在运行 PostgreSQL 9.2。
以下是一段我巨大而丑陋查询的示例：

with cte as(
select ....... from aTable 
),cte1 as (
select ..... from bTable inner join cte using(anID)
),update_cte as(
update cte set aField=(select somthing from cte1)
)
select * from cte

我需要创建一个带有最终结果的视图。

在执行上述操作时，我遇到了以下错误。

ERROR:  relation "cte" does not exist

我知道我在做一些不好的事情。希望您能理解我从上面的查询中试图实现的内容。请建议任何替代方法。

感谢回复。

注意：实际查询请参见此链接。

- user3814846

---

不，你在这里试图做什么根本不清楚。看起来你想修改第一个cte的内容；但那是为了另一个查询而计算出的瞬态中间值。 - SingleNegationElimination

我需要使用 WITH RECURSIVE 吗？ - user3814846

您可能需要在此处提供完整的未编辑查询。 - Craig Ringer

@CraigRinger 这真的是一个很大的问题，所以我在这里放了一个样例。 - user3814846

@CraigRinger 刚刚看到实际的查询。 - user3814846

Answer 1

with cte as(
select ....... from aTable
),update_cte as(
update cte set aField=(select somthing from cte1)
)

你不能这样做。

在PostgreSQL中，UPDATE语句不能引用公共表表达式（CTE），因为CTE是实例化的，而不仅仅是底层数据的视图。（有时这真的很烦人，但它就是这样的）。

你可以：

CREATE TEMPORARY VIEW someview AS SELECT ... FROM atable;

UPDATE someview SET afield = ...

如果您希望使用自动更新的视图，那么这将适用于支持此功能的新版PostgreSQL。我认为9.2版本支持。

否则，我认为您需要类似这样的内容：

WITH cte1 as (
  select ..... from bTable inner join cte using(anID)
)
update aTable 
set aField=(select somthing from cte1)
WHERE ... where clause from cte ...
RETURNING *;

但是，真的，请不要将你的CTE术语命名为cte、cte1等。给它们有用的、描述性的名称，告诉你它们是什么。这就像变量命名为a到x的程序一样...下一个需要维护你的代码的人，或者任何你寻求帮助的人，都会不喜欢它。

- Craig Ringer

---

如果您想在支持自动更新视图的较新版本的PostgreSQL上使用它，那应该可以。我认为9.2版本可以。您是指“物化视图”吗？ - user3814846

创建临时视图someview，其选择语句为SELECT ... FROM atable; 在此过程中，选择在两个日期之间收集的数据，因此它真的有用吗？实际上，查询的主要目标是查找所有产品的库存和销售情况，用户可以根据这些日期选择从和到日期，我需要查询表格。 - user3814846

还有一件事：UPDATE someview SET afield = ... > 是否可以更新 VIEW？ - user3814846

@keet 是的，这就是我写的原因。请参阅文档 http://www.postgresql.org/docs/current/static/sql-createview.html 。至于第一个评论-在这样一般的条件下，我不知道。我能给你展示的只有“好吧，你可以这样做”，而你需要确定它是否符合你的实际需求。 - Craig Ringer

现在可以在CTE（通用表达式）中使用UPDATE。请参阅文档中的"Data-Modifying Statements in WITH"一节：链接。 - Adrian B.