Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Query on Update Query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Query on Update Query
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: 1278
Location: Belgium

PostPosted: Fri Aug 13, 2010 1:54 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us