|
View previous topic :: View next topic
|
| Author |
Message |
Poha Eater
New User
Joined: 31 Aug 2016
Posts: 74
Location: India
|
|
|
|
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 |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
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 |
|
|
Poha Eater
New User
Joined: 31 Aug 2016
Posts: 74
Location: India
|
|
|
|
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.
 |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Thanks for letting know! |
|
| Back to top |
|
|
|
|
