Page 1 of 1

Sequence Number update for the duplicate records

Posted: Tue Aug 09, 2016 4:41 pm
by pawan.konduru
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