postgresql 使用连接更新 Redshift 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38785868/
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
Redshift table update with join
提问by oxy_js
I have 3 table t1, t2 and t3.
t1 has 2 column-> id1, val1
我有 3 个表 t1、t2 和 t3。
t1 有 2 列-> id1, val1
t2 -> id2, val2
t3 -> id3, val3
If id1=id2 and id2 = id3
then I need to update val1 ad val3. But I have repeating id1 and each should have same val3
然后我需要更新 val1 和 val3。但是我有重复的 id1 并且每个都应该有相同的 val3
I am using
我在用
update t1
inner join t2 on t1.id1 = t2.id2
inner join t3 on t2.id2 = t3.id3
set t1.val1 = t3.val3
;
But not able to do this.
但无法做到这一点。
回答by moertel
The correct syntax is:
正确的语法是：
UPDATE table_nameSET column = { expression| DEFAULT } [,...]
[ FROM fromlist]
[ WHERE condition]
UPDATE table_nameSET column = {表达式| DEFAULT } [,...]
[ FROM fromlist]
[ WHERE条件]
So your UPDATEstatement should look as follows:
因此，您的UPDATE声明应如下所示：
update t1 set val1 = val3
from t2 inner join t3 on t2.id2 = t3.id3
where t1.id1 = t2.id2
;
See the Redshift documentationand their comprehensive UPDATEexamples.
请参阅Redshift 文档及其综合UPDATE示例。
回答by Jie
I needed to get values from the other table, t2.val3, on Redshift. I used the following
我需要从 Redshift 上的另一个表 t2.val3 中获取值。我使用了以下
update t1
set val1 = t2.val3
from t2 join t1 t on t.id = t2.id;
I have to re-name t1. Otherwise Redshift complains.
我必须重命名 t1。否则 Redshift 会抱怨。
