Oracle 10 how to update a table as a sum

Question

I have two tables with identical columns ID, A, B, C.

I need to ADD to TableX the values from TableY for the corresponding ID's. I know how to do this for a SINGLE update as follows:

update TableX x
set x.A= x.A +
    (select y.A
    from TableY y
    where x.id= y.id)
where exists (select y.id
    from TableY y
    where x.id = Y.id).

But how to modify this statement so that I can update multiple columns as sums?

TIA

possible duplicate of [Oracle SQL: Update a table with data from another table](http://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table) — Bulat, Jul 30 '15 at 18:25

score 2 · Accepted Answer · answered Jul 30 '15 at 18:13

update TableX x
set (x.A, x.B, x.C) = (select y.A + x.A,
                              y.B + x.B,
                              y.C + x.C
                       from TableY y 
                       where x.id= y.id)
where exists (
  select y.id 
  from TableY y 
  where x.id = Y.id)

score 1 · Answer 2 · answered Jul 30 '15 at 18:24

1

merge into tableX x
using (select * from tableY) y
on (x.id = y.id)
when matched then update set
  x.a = x.a + y.a, x.b = x.b + y.b, x.c = x.c + y.c;

^SQLFiddle

You could use merge, especially if you want also insert non existing rows.

answered Jul 30 '15 at 18:24

Ponder Stibbons

14,723
2
21
24

I have never used Merge, but I will definitely experiment with it. Thanks. – PeteG Jul 30 '15 at 18:58
If you are interested in further reading about `merge` statement then I advise this old, good article ["the merge statement in oracle 9i"](http://www.oracle-developer.net/display.php?id=203) for start and [this thread](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5318183934935) from _Ask Tom_ site. If you are working with Oracle it's definitely something you should know. – Ponder Stibbons Jul 30 '15 at 22:17

score 0 · Answer 3 · answered Jul 30 '15 at 18:28

0

We can do this the following way in Teradata:

Update X
From TableX X,
(Select A,B,C From TableY Where id in (select id from TableX group by 1)) S
set
A=A+S.A
,B=B+S.B
,C=C+S.C
where exists (select y.id
from TableY y
where x.id = Y.id)

answered Jul 30 '15 at 18:28

Sudhanshu Agrawal

412
3
9

You might be able to do that in Teradata, but you can't do that in Oracle (which is what this question is about) – Jul 30 '15 at 18:37

Oracle 10 how to update a table as a sum

3 Answers3