0

I have two tables :

Table1 (col1, col2, col3, col4, val1, status)
Table2 (col1, col2, col3, col4, val2)

For Table1 and Table2, The columns (col1, col2, col3, col4) are the composite primary key.

Table2 may have duplicated rows, for that reason I would like to group by (col1, col2, col3, col4) and sum(val2)

After that, I would like to update Table1.val1 by the value of sum(Table1.val2) when Table1.col1 = Table2.col1 and Table1.col2 = Table2.col2 and Table1.col3 = Table2.col3 and Table1.col4 = Table2.col4 and status= 'V'.

I did something like that:

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 
      And status= 'V'
    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)

Could someone help me please? Thank you

  • Why do you want to store redundant information? – Aaron Bertrand Dec 2 '14 at 12:39
2

If you have a primary key, you don't have duplicate rows.

In Oracle, you do multi-table UPDATEs by using MERGE, and not UPDATE, because that is inefficient.

merge into table1 
using (select col1, col2, col3, col4, sum(val2) sum_val2 from table2 group by col1, col2, col3, col4) table2
on (table1.col1 = table2.col1 and table1.col2 = table2.col2 and table1.col3 = table2.col3 and table1.col4 = table2.col4)
when matched then update set table1.val1 = table2.sum_val2, table1.status = 'V';

If col1, col2, col3, col4 are really a composite primary key, no need to GROUP BY and SUM:

merge into table1 
using table2
on (table1.col1 = table2.col1 and table1.col2 = table2.col2 and table1.col3 = table2.col3 and table1.col4 = table2.col4)
when matched then update set table1.val1 = table2.val2, table1.status = 'V';
0

Grouping a Primary Key is wasted time, since you get groups of one record only. Hence, the SUM function is unnecessary, you just need to get the column.

But, the real problem you are trying to solve, is a bit unclear. If you ever have 2 tables in a database, with EXACTLY the same Primary Key, there is something wrong. Obviously, they should be 1 table only, and then you don't need elaborate UPDATE statements. You may need an UPDATE statement, but nothing like above.

Explain us why you have identical Primary Keys in different tables.

  • The Table1 has (col1-col4) as a primary key, Table2 has (col1-col4) as primary key + (another column that differenciate called col5). i didn't want to complicate the question more and more. this is why i just put the necessary elements for the question. – Mehdi Boussarhane Dec 3 '14 at 8:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.