PostgreSQL：如何在CTE中更新行 [英] PostgreSQL:how to update rows in CTE

问题描述

正在运行 PostgreSQL 9.2 。以下给出的
是我的庞大而丑陋的 查询

am running PostgreSQL 9.2.
below given is a sample of my huge and uglyquery

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

我需要创建一个视图，最后一个 result 。

在执行上面的操作时会收到下面的错误。

while executing the above am getting an error which is below.

ERROR:  relation "cte" does not exist

我知道我
希望您能从上述 query 理解要实现的目标。

因此，请提出其他替代方法。

I know am doing something bad.
hope you can understand what am trying to achieve from the above query.
So please suggest any alternative method for it.

非常感谢回复。

注意：实际查询

推荐答案

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

您不能那样做。

UPDATE 可能未引用PostgreSQL中的CTE术语，因为CTE已实现。它们不仅是基础数据的视图。 （有时确实很烦人，但是就是这样。）

An UPDATE may not reference a CTE term in PostgreSQL, as CTEs are materialized. They aren't just views over the underlying data. (That's sometimes really annoying, but that's how it is).

您可以：

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

UPDATE someview SET afield = ...

如果需要的话；可以在支持自动更新视图的新PostgreSQL版本上使用。我认为9.2确实可以。

if you want; that'll work on newer PostgreSQL versions that support automatically updatable views. I think 9.2 does.

否则，我想您需要类似的东西：

Otherwise, I think you want something like:

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 的变量...下一个必须维护您的代码的人，或者您寻求帮助的任何人都会不喜欢它。

but really, please don't call your CTE terms cte, cte1, etc. Give them useful, descriptive names that tell you what they are. It's like programs full of variables named a through x ... the next person who has to maintain your code, or anyone you ask for help, will not like it.

这篇关于PostgreSQL：如何在CTE中更新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！