PostgreSQL 更新语句中的内连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24428152/
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
Inner join within update statement in PostgreSQL
提问by Meem
I have table called temp_table which consist of following rows:
我有一个名为 temp_table 的表，它由以下几行组成：
cola colb result
----------------
p4 s1 0
p8 s1 0
p9 s1 0
p5 f1 0
p8 f1 0
Now I need to update result column with the count(*) of colb. For which i am trying the following query:
现在我需要用 colb 的计数（*）更新结果列。为此，我正在尝试以下查询：
update tem_table
set result = x.result
from tem_table tt
inner join(select colb,count(*) as result from tem_table group by colb) x
on x.colb = tt.colb;
And selecting distinct colb and result from temp_table:
并从 temp_table 中选择不同的 colb 和结果：
select distinct colb,result from tem_table;
Getting output:
获取输出：
colb result
-----------
s1 3
f1 3
But the expected output is:
但预期的输出是：
colb result
-----------
s1 3
f1 2
I am not getting where I am getting wrong in my query? Please help me.Thanks
我不明白我在查询中出错的地方？请帮助我。谢谢
回答by a_horse_with_no_name
You should not repeat the table to be updated in the fromclause. This will create a cartesian self join.
您不应在from子句中重复要更新的表。这将创建一个笛卡尔自连接。
Quote from the manual:
引自手册：
Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list)
请注意，目标表不得出现在 from_list 中，除非您打算进行自联接（在这种情况下，它必须以别名出现在 from_list 中）
(Emphasis mine)
（强调我的）
Unfortunately UPDATEdoes not support explicit joins using the JOINkeyword. Something like this should work:
不幸的UPDATE是不支持使用JOIN关键字的显式连接。这样的事情应该工作：
update tem_table
set result = x.result
from (
select colb,count(*) as result
from tem_table
group by colb
) x
where x.colb = tem_table.colb;
