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

Need Guidance regarding UPDATE query


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

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Mon Feb 12, 2018 11:35 pm
Reply with quote

Hi All,

I am trying to write an UPDATE query for updating the rows which are fulfilling the below criteria :

a) Company Code & Master Id should be present in both the tables - DEPCT table & CONTE table
b) TOTAL_NUM_OF_LAPSE should be less than 0 in DEPCT table for those master_id & Company Code which are present in both tables
c) SEGMENT_ID should be spaces in DEPCT table for master_id & Company Code which are present in both tables
d) POLICY_STATUS should not be equal to 'P' in CONTE table for master_id & Company Code which are present in both tables

This is the UPDATE query which i have written :
Code:
UPDATE V1LFODCJ.DEPCT                           
SET TOTAL_NUM_OF_LAPSE = 0                     
WHERE MASTER_ID                                 
IN (SELECT B.MASTER_ID FROM V1LFODCJ.CONTE A,   
    V1LFODCJ.DEPCT B WHERE                     
       A.MASTER_ID = B.MASTER_ID AND           
       A.COMPANY_CODE = B.COMPANY_CODE AND     
       B.TOTAL_NUM_OF_LAPSE < 0 AND             
       B.SEGMENT_ID = ' ' AND                   
       A.POLICY_STATUS <> 'P');   



This is the data in DEPCT table before running the UPDATE query :
Code:
COMPANY_CODE  MASTER_ID        SEGMENT_ID  TOTAL_NUM_OF_LAPSE
CHAR(3)       CHAR(15)         CHAR(2)               DEC(5,0)
------------  ---------------  ----------  ------------------
**************************** TOP OF DATA ********************
CGA                0000013477                              0.
CGA                0000013477                             -1.
CGA                0000013477                             -1.
CGA                0000013477  JA                         -1.
001                  G1105177                             -1.
001                  G1105177                             -1.
001                  G1105177                             -1.
001                  G1105177  IA                         -1.
001                  G1105177  JA                         -1.



This is the data in CONTE table :

Code:
COMPANY_CODE  MASTER_ID        POLICY_STATUS
CHAR(3)       CHAR(15)         CHAR(1)     
PRIMARY-----  PRIMARY--------  -------------
**************************** TOP OF DATA ***
CGA                0000013477  A
001                  G1105177  A             



This is the data in DEPCT table after running the UPDATE query :
Code:
COMPANY_CODE  MASTER_ID        SEGMENT_ID  TOTAL_NUM_OF_LAPSE
CHAR(3)       CHAR(15)         CHAR(2)               DEC(5,0)
------------  ---------------  ----------  ------------------
**************************** TOP OF DATA *********************
CGA                0000013477                              0.
CGA                0000013477                              0.
CGA                0000013477                              0.
CGA                0000013477  JA                          0.
001                  G1105177                              0.
001                  G1105177                              0.
001                  G1105177                              0.
001                  G1105177  IA                          0.
001                  G1105177  JA                          0.

The TOTAL_NUM_OF_LAPSE field is becoming 0 for all the values even for the rows where SEGMENT_ID is not equal to spaces.

Expected result in DEPCT table :
Code:
COMPANY_CODE  MASTER_ID        SEGMENT_ID  TOTAL_NUM_OF_LAPSE
CHAR(3)       CHAR(15)         CHAR(2)               DEC(5,0)
------------  ---------------  ----------  ------------------
**************************** TOP OF DATA ********************
CGA                0000013477                              0.
CGA                0000013477                              0.
CGA                0000013477                              0.
CGA                0000013477  JA                         -1.
001                  G1105177                              0.
001                  G1105177                              0.
001                  G1105177                              0.
001                  G1105177  IA                         -1.
001                  G1105177  JA                         -1.


I have also tried the below UPDATE query but output is still same. It has changed the TOTAL_NUM_OF_LAPSE to 0 for all the rows including the ones where SEGMENT_ID is not equal to spaces.
Code:
UPDATE V1LFODCJ.DEPCT                                         
SET TOTAL_NUM_OF_LAPSE = 0                                     
WHERE EXISTS                                                   
(SELECT A.COMPANY_CODE,A.MASTER_ID FROM V1LFODCJ.CONTE A,     
            V1LFODCJ.DEPCT B                                   
 WHERE A.COMPANY_CODE = B.COMPANY_CODE AND                     
       A.MASTER_ID = B.MASTER_ID AND                           
       B.TOTAL_NUM_OF_LAPSE < 0 AND                           
       B.SEGMENT_ID = ' ' AND                                 
       A.POLICY_STATUS <> 'P');                               


Please let me know what i am missing here and how can i achieve the expected output.

Thanks a lot in Advance !
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Feb 13, 2018 12:56 am
Reply with quote

You might want to move your DEPCT checks outside your sub query (shown in the original post) like this:
Code:
UPDATE V1LFODCJ.DEPCT                           
 SET TOTAL_NUM_OF_LAPSE = 0                     
 WHERE  TOTAL_NUM_OF_LAPSE < 0 AND             
        SEGMENT_ID = ' ' AND   
 MASTER_ID                                 
 IN (SELECT B.MASTER_ID FROM V1LFODCJ.CONTE A,   
     V1LFODCJ.DEPCT B WHERE                     
        A.MASTER_ID = B.MASTER_ID AND           
        A.COMPANY_CODE = B.COMPANY_CODE AND     
        A.POLICY_STATUS <> 'P');   
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Sat Feb 17, 2018 5:46 pm
Reply with quote

Thanks a lot Arun for taking a look and pointing me in right direction. Your query worked fine and i am able to get the expected output.

icon_smile.gif
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Feb 20, 2018 9:43 am
Reply with quote

Thanks for letting know!
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 Reading subsequent rows in a query. DB2 12
No new posts SYSIBM Tables Query DB2 8
No new posts SKIP LOCKED DATA in UPDATE statement DB2 9
No new posts Need Help with on of the coalesce query DB2 4
No new posts Group comparison/update between two f... DFSORT/ICETOOL 10
Search our Forums:

Back to Top