sql - 甲骨文 SQL : Updating a column with SUM query of another table

Question

我有两个表:Table1 有 5 列(col1、col2、col3、col4、val1)，Table2 有 Table1 有 5 列(col1、col2、col3、col4、val2)。1. Table1.val1 不包含任何值。2.对于Table1和Table2，col1、col2、col3、col4相同，都是主键。

我想做的是在 Table1.col1 = Table2.col1 and Table1.col2 = Table2.col2 and Table1.col3 = Table2.col3 and Table1.col4 = Table2.col4.

我做了类似的事情:

UPDATE Table1
SET val1 = (

select t_sommevbrute.sumvalbrute from (
Select  col1,col2,col3,col4,SUM(val2) sumvalbrute
From Table2
Where col3 = 2014 And col2=51 
GROUP BY col1, col2, col3, col4) t_sommevbrute

WHERE Table1.col1 = t_sommevbrute.col1
and Table1.col2 = t_sommevbrute.col2
and Table1.col3 = t_sommevbrute.col3
and Table1.col4 = t_sommevbrute.col4)

但与这个问题相关:Oracle SQL: Update a table with data from another table ，我应该有 WHERE EXISTS 子句。

请帮忙!!谢谢。

Answer 1

您可以通过以下方式执行此操作:-

使用临时表:-首先创建临时表:-

Create table temp1 as
Select  col1,col2,col3,col4,SUM(val2) as sumvalbrute
     From table2
     Where col3 = 3 And col2=2 
     GROUP BY col1, col2, col3, col4;

然后使用临时表更新主表1:-

UPDATE table1 SET table1.val1 = (SELECT temp1.sumvalbrute
                                  FROM temp1 
                                  WHERE Table1.col1 = temp1.Col1
                                  AND Table1.col2 = temp1.Col2
                                  AND Table1.col3 = temp1.Col3
                                  AND Table1.col4 = temp1.Col4);

SQL fiddle :- http://sqlfiddle.com/#!4/4864d/5

不使用临时表:-

UPDATE table1 SET table1.val1 = (SELECT temp1.sumvalbrute
                                 FROM 
                                 (Select  col1,col2,col3,col4,SUM(val2) as sumvalbrute
                                  From table2
                                  Where col3 = 3 And col2=2 
                                  GROUP BY col1, col2, col3, col4) temp1 
                                  WHERE Table1.col1 = temp1.Col1
                                  AND Table1.col2 = temp1.Col2
                                  AND Table1.col3 = temp1.Col3
                                  AND Table1.col4 = temp1.Col4);

SQL fiddle :- http://sqlfiddle.com/#!4/c9286/2