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
 

 

Update using Inner join

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
bachi234

New User


Joined: 15 May 2007
Posts: 6
Location: VA

PostPosted: Fri Sep 21, 2012 9:23 pm    Post subject: Update using Inner join
Reply with quote

EXperts, Could you please share your thought on what i should be adding in order to make this update??

For all the resulting rows that i get from this sql i want to update ICS.CPT with DCS.CPT.
Code:
SELECT      BP.LBC          AS BP_CASE               
           ,ILB.CN           AS ILB_CASE               
           ,DTL.LBCN        AS DTL_CASE               
           ,BCS.CPT         AS BP_PTD                 
           ,ICS.CPT         AS ILB_PTD               
           ,DCS.CPT         AS DTL_PTD               
       FROM DB2TEST.GLH   BP                           
           ,DB2TEST.GLH   ILB                         
           ,DB2TEST.GLH   DTL                         
           ,DB2TEST.CM    BCS                         
           ,DB2TEST.CM    ICS                         
           ,DB2TEST.CM    DCS                         
     WHERE  BP.OC      = 'CV'                           
       AND  BP.CUI     = 'BP'                   
       AND  BP.LBS     = 'A'                       
       AND ILB.BLC     = BP.LBC               
       AND ILB.CUI     = 'IB'                     
       AND ILB.LBS     = 'A'                       
       AND DTL.PCN     = ILB.LCN         
       AND DTL.CUI     = 'DC'                     
       AND DTL.LBS     = 'A'                       
       AND BCS.CN      = BP.LCN         
       AND ICS.CN      = ILB.LCN         
       AND DCS.CN      = DTL.LCM         
       AND NOT ICS.CPT = DCS.CPT


I tried using the below query to update
Code:
UPDATE   ICS
      SET            ICS.CPT = DCS.CPT
     FROM DB2TEST.GLH   BP
                INNER JOIN  DB2TEST.GIH   ILB     ON   
                                 ILB.BLC    = BP.LBC
                INNER JOIN  DB2TEST.GLH   DTL     ON 
DTL.PCN      =         ILB.LBCN
       INNER JOIN  DB2TEST.CM         BCS     ON 
BCS.CN             = BP.LBCN
        INNER JOIN  DB2TEST.CM         ICS     ON
 ICS.CN             = ILB.LBCN
        INNER JOIN  DB2TEST.CM         DCS     ON  DCS.CN             = DTL.LBCN
     WHERE  BP.OC       = 'CV'
       AND  BP.CUI     = 'BP'
       AND  BP.LBS     = 'A'                       
       AND ILB.CUI     = 'IB'                     
       AND ILB.LBS     = 'A'                       

       AND DTL.CUI     = 'DC'                     
       AND DTL.LBS     = 'A'                       

       AND NOT ICS.CPT = DCS.CPT

but db2 says SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD FROM. Could you please help me how to make this work???

Code'd
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Sep 21, 2012 9:27 pm    Post subject:
Reply with quote

yes, would be glad to help your.

suggest that you look at the proper syntax of the UPDATE statement.
(BTW the proper syntax can be found in an SQL Reference Manual)

a hint, you are going to have to code a WHERE clause that contains a subquery.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Sep 21, 2012 9:40 pm    Post subject:
Reply with quote

If this is imbedded SQL,
I would suggest DECLAREing a CURSOR for the first SQL
and include in the SELECT List,
the necessary columns to create an unique SELECT
which would then be used in a simple singleton UPDATE of ICS
with a WHERE Clause using the host variables
containing the primary key and the value for the SET clause.

and saving the expense of the two sqls with the joins.

of course, you could learn to write the mass-update SQL properly
and then I imagine, you would not need the first SQL.
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 Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 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 Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am


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