Sequence Number update for the duplicate records

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
pawan.konduru
Member
Posts: 13
Joined: Mon Feb 17, 2014 11:35 am
Location: Hyderabad

Sequence Number update for the duplicate records

Post by pawan.konduru » Tue Aug 09, 2016 4:41 pm

Hi,

We have a table as below with 3 columns V_NUMBER,D_CREATED and N_PGM

EC599980| 10/12/2015 12:00:00 AM | PGM1
EC599980| 10/09/2015 12:00:00 AM | PGM2
EC492925| 10/26/2015 12:00:00 AM | PGM1
EC503933| 11/15/2015 12:00:00 AM | PGM1
EC503933| 11/12/2015 12:00:00 AM | PGM1
EC503933| 11/11/2015 12:00:00 AM | PGM1


We have added a new column I_SEQ with NOT NULL DEFAULT, so the values in the new column are ZEROs

EC599980 | 10/12/2015 12:00:00 AM | PGM1 | 0
EC599980 | 10/09/2015 12:00:00 AM | PGM2 | 0
EC492925 | 10/26/2015 12:00:00 AM | PGM1 | 0
EC503933 | 11/15/2015 12:00:00 AM | PGM1 | 0
EC503933 | 11/12/2015 12:00:00 AM | PGM1 | 0
EC503933 | 11/11/2015 12:00:00 AM | PGM1 | 0

now we need to update the I_SEQ with the Sequence Number for all the duplicate records (V_NUMBER) as below the latest record

EC599980 | 10/09/2015 12:00:00 AM | PGM1 | 1
EC599980 | 10/12/2015 12:00:00 AM | PGM2 | 2
EC492925 | 10/26/2015 12:00:00 AM | PGM1 | 1
EC503933 | 11/11/2015 12:00:00 AM | PGM1 | 1
EC503933 | 11/12/2015 12:00:00 AM | PGM1 | 2
EC503933 | 11/15/2015 12:00:00 AM | PGM1 | 3

can someone help to write the update query
Pawan K

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others