IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Query on Update Query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
safiya

New User


Joined: 12 Aug 2010
Posts: 1
Location: India

PostPosted: Thu Aug 12, 2010 8:10 pm
Reply with quote

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.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Aug 13, 2010 1:54 pm
Reply with quote

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)...)"
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
No new posts Issue with EXEC CICS QUERY SECURITY c... CICS 6
Search our Forums:

Back to Top