safiya
New User
Joined: 12 Aug 2010 Posts: 1 Location: India
|
|
|
|
I have three tables say Table A, Table BA, Table C and Table design is given below as :
Table A - Columns: A1, A2, A3, A4
Table B - Columns : A1,A2,A3,B1
Table C - Coulmns: A1,B1,A4
Here A maps to B using A1,A2,A3
B maps to C using A1,B1
can any one help me to designing an update query to update field A4 in table A with A4 in table C for the corresponding rows. |
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
for db2 9
Code: |
update testc C
set c.a4 = (select a.a4
from testB B
join testA A on a.a1 = b.a1 and a.a2 = b.a2 and a.a3 = b.a3
where b.a1 = C.a1 and b.b1 = c.b1
fetch first 1 row only)
where exists(select a.a4
from testB B
join testA A on a.a1 = b.a1 and a.a2 = b.a2 and a.a3 = b.a3
where b.a1 = C.a1 and b.b1 = c.b1) |
or for db2 9 and earlier versions
Code: |
update testc C
set c.a4 = (select max(a.a4)
from testB B
join testA A on a.a1 = b.a1 and a.a2 = b.a2 and a.a3 = b.a3
where b.a1 = C.a1 and b.b1 = c.b1)
where exists(select a.a4
from testB B
join testA A on a.a1 = b.a1 and a.a2 = b.a2 and a.a3 = b.a3
where b.a1 = C.a1 and b.b1 = c.b1) |
if because of RI there will be always AT LEAST 1 corresponding row in TabA for each row of TabC , you can omit the WHERE EXISTS()
if because of RI there will be always MAXIMUM 1 corresponding row in TabA for each row of TabC , you can omit the "fetch first 1 row only" or max()-function
if more than 1 corresponding row you will have to decide which value of a.a4 you want. the example give : "the first one found" or "the max()".
Other possibilities are fe. adding a "where ts = (select max(ts)...)" |
|