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 Query

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

Active User


Joined: 17 Mar 2006
Posts: 171
Location: Bangalore

PostPosted: Mon Mar 24, 2008 12:42 pm    Post subject: UPDATE Query
Reply with quote

I have a table ABC

Code:
ref_nbr       prod  usoc  tn         order_nbr    date
0004352641 LPIC  LPIC  8187899951517 C62767057 10/29/2007           
0004352642 LDPIC LDPIC 8187899951517 C62767057 10/29/2007           
0004352634 CID   CNM   8187899951517 C62767057 10/29/2007           
0004352635 CID   CNM   8187899951517 C62767057 10/29/2007           
0004352636 CCS   ESM   8187899951517 C62767057 10/29/2007           
0004352637 CCS   NWL   8187899951517 C62767057 10/29/2007           
0004352638 CCS   RAF   8187899951517 C62767057 10/29/2007           
0004352639 LPIC  LPIC  8187899951517 C62767057 10/29/2007           
0004352640 LDPIC LDPIC 8187899951517 C62767057 10/29/2007           
0004352631 LB19B LB19B 8187899951517 C62767057 10/29/2007           
0004352632 KSTTU KSTTU 8187899951517 C62767057 10/29/2007           
0004352633 PGOHN PGOHN 8187899951517 C62767057 10/29/2007


I want to check the duplicates for the combination of A.PROD,A.USOC,A.TN,B.ORDER_NBR,B.DATE

Table XYZ is having STATUS column
I want to get the ref_nbr's to update the STATUS Coulmn of XYZ to DEL for the combination of above mentioned duplicates


For example shown above

Code:
prod  usoc          tn     order_nbr     date        count(*)
LDPIC LDPIC 8187899951517 C62767057 10/29/2007              2                       
CID   CNM   8187899951517 C62767057 10/29/2007             2       
LPIC  LPIC  8187899951517 C62767057 10/29/2007             2 


If I do update with Ref_nbr I should get only 6 records should update
How I can achieve this?

I have written SQL as

Code:
SELECT A.PROD,A.USOC,A.TN,B.ORDER_NBR,B.DATE
FROM abc A,
     xyz B,
     jkl C
WHERE A.REF_NBR = B.REF_NBR
AND   B.REF_NBR = B.INWD_REF_NBR
AND   C.REF_NBR = A.REF_NBR
AND   (B.SO_CMPL_DATE >= '01-OCT-2007'
AND   B.SO_CMPL_DATE <= '30-NOV-2007')
AND   B.STATUS_CD <> 'DEL'
AND   B.SPECIAL_PROCESS_IND IN ('UPR1 ','UPR2 ','UPR3 ')
GROUP BY A.PROD,A.USOC,A.TN,B.ORDER_NBR,B.DATE
HAVING COUNT(*) > 1


but from the above I am not able to get the Ref_nbrs to update the XYZ table.
Back to top
View user's profile Send private message

Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Tue Mar 25, 2008 11:18 am    Post subject:
Reply with quote

Shrivatsa,

Try out these query. I did not test this one and also I have made an assumption that ref_nbr is unique from ur data.

Update ABC A set A.status = 'D'
where A.ref_nbr in( select B.ref_nmbr from XYZ B
Where exists ( select 1 from XYZ C
where b.PROD = C.PROD
and b.USOC = C.USOC
and b.ORDER_NBR = C.ORDER_NBR
and b.DATE = C.DATE
and B.ref_nbr <> C.ref_nbr ));

Thanks,
Prajesh
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 171
Location: Bangalore

PostPosted: Tue Mar 25, 2008 5:01 pm    Post subject: Reply to: UPDATE Query
Reply with quote

Here is the query which I used for the problem

Code:
UPDATE XYZ
SET STATUS = 'DEL'
WHERE REF_NBR IN
(SELECT REF_NBR FROM (
    SELECT B.REF_NBR, B.STATUS,
         COUNT(B.STATUS) OVER (PARTITION BY A.PROD,A.USOC_CD,A.TN,B.ORDER_NBR,B.DATE) CNT
    FROM XYZ B
        ,ABC A
        ,JKL C
   WHERE B.REF_NBR = A.REF_NBR
   AND   B.REF_NBR = B.REF_NBR1
   AND   C.REF_NBR = A.REF_NBR
   AND   (B.DATE >= '01-OCT-2007'
   AND   B.DATE <= '31-DEC-2007')
   AND   B.STATUS <> 'DEL'
   AND   B.SP_IND IN ('UPR1 ','UPR2 ','UPR3 '))
WHERE CNT > 1)
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 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 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


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