Sequence Number update for the duplicate records
Posted: 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
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