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: 1280
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm


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