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

Question

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.

Answer 1

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

Answer 2

You can simplify your query greatly:

UPDATE Table1
    SET val1 = (select SUM(val2)
                from Table2 t
                where Table1.col1 = t.col1 and
                      Table1.col2 = t.col2 and
                      Table1.col3 = t.col3 and
                      Table1.col4 = t.col4
               )
    Where col3 = 2014 And col2 = 51;

You could use where exists. The purpose is to prevent NULL values when there is no match. However, I think pulling the where clause outside probably solves this problem. I note that the where clause is using the same values as the correlation conditions.

Answer 3

You can try this:

UPDATE t1 SET t1.val1 = SUM(t2.val2)
FROM table1
INNER JOIN table2 t2 ON t2.col1 = t1.col1 
and t2.col2 = t1.col2 
and t2.col3 = t1.col3 
and t2.col4 = t2.col4

Answer 4

This kind of update can be achieved ALSO using MERGE statement:

Merge into Table1 using ( select SUM(val2) as val2, t.col1, t.col2, t.col3, t.col4
                            from Table2 t
                           group by t.col1, t.col2, t.col3, t.col4
                         ) t -- This is the alias for table 2
   on (    Table1.col1 = t.col1 
       and Table1.col2 = t.col2 
       and Table1.col3 = t.col3 
       and Table1.col4 = t.col4
      )
when matched then 
  UPDATE Table1.val1 = t.val2 --HERE IS YOUR **UPDATE**
where Table1.col3 = 2004
  and Table1.col2 = 51
 ;

commit;

Please don't forget to ADD ALIASES to the tables, and a good COMMIT after this code This solution avoid you from create temporary tables, and works pretty well

Best regards.