Oracle 10 how to update table as sum
I have two tables with the same columns ID
, A
, B
, C
.
I need to add to the TableX
values from TableY
for the relevant ones ID
. I know how to do it for SINGLE update like this:
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 do I change this statement so that I can update multiple columns as sums?
TIA
+3
PeteG
source
to share
3 answers
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)
+2
sstan
source
to share
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 can use merge , especially if you want to insert non-existing lines.
+1
Ponder stibbons
source
to share
We can do it in Teradata like this:
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)
0
Sudhanshu Agrawal
source
to share