Sql – Oracle SQL : Updating a column with SUM query of another table

oracleselectsql

I have two tables :
Table1 with 5 columns (col1, col2, col3, col4, val1) and Table2 with Table1 with 5 columns (col1, col2, col3, col4, val2).
1. The Table1.val1 doesn't contain any value.
2. For both Table1 and Table2, col1, col2, col3 and col4 are the same and they are the primary key.

What I would like to do, is to update the Table1.val1 with the sum(Table2.val2) when Table1.col1 = Table2.col1 and Table1.col2 = Table2.col2 and Table1.col3 = Table2.col3 and Table1.col4 = Table2.col4.

I did something like :

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)

But related to this question: Oracle SQL: Update a table with data from another table , I should have WHERE EXISTS clause.

Any help please!!
Thanks.

Best Answer

You can do this in following way:-

With Use of Temp Table:- First Create Temp Table:- 

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;

And then use Temp table to update Main Table1:-

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

WithOt Use of Temp Table:-

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

Related Topic