SQL 根据teradata中表2的值更新表1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7039948/
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
Update table1 based on value from table 2 in teradata
提问by naveen
I have two tables like this
我有两张这样的桌子
I would like to insert from Table1 to Table2 here. This is how I want it.
我想在这里从 Table1 插入到 Table2。这就是我想要的。
Take MOU = 10. It has num1 and hour1 in the same row. I would like to insert it into the cell that is at the same row as that of num1 and same column as that of hour1.
取 MOU = 10。它在同一行中有 num1 和 hour1。我想将它插入到与 num1 位于同一行且与 hour1 位于同一列的单元格中。
How could I do that?
我怎么能那样做？
Disclaimer: I am not offering any code here because I am unsure of how to write this query. I sure do know to write a simple update. I am a teracota newbie.
免责声明：我在这里不提供任何代码，因为我不确定如何编写此查询。我确实知道写一个简单的更新。我是teracota新手。
采纳答案by naveen
This worked.
这奏效了。
UPDATE a
FROM table2 a, table1 b
SET hour1=b.mou
WHERE a.access_method_id=b.access_method_id
AND hour='hour1'
Did the same for each hours. Not very elegant. But this is all I could get.
每个小时都做同样的事情。不是很优雅。但这就是我所能得到的。
回答by Derek Kromm
Here is some generic SQL that should get the job done.
这是一些应该可以完成工作的通用 SQL。
insert into table2(access_method_id, hour1, hour2, ...)
select
access_method_id,
sum(case when hour='HOUR1' then MOU else 0 end) as hour1,
sum(case when hour='HOUR2' then MOU else 0 end) as hour2,
...etc
from
table1
group by
access_method_id
回答by Aniruth
try this!
尝试这个！
update table2 t2
from (select
access_method_id,
sum(case when hour='HOUR1' then MOU else 0 end) as hour1,
sum(case when hour='HOUR2' then MOU else 0 end) as hour2,
...etc
from
table1) t1
set
t2.hour1=t1.hour1,
t2.hour2=t1.hour2,
t2.hour3=t1.hour3,
...etc
where t2.access_method_id=t1.access_method_id;
