How to change the contribution code for duplicate records

Question

How to change the contribution code for duplicate records

Navigation

#1 by (0 votes)

0

I have a query that returns me 4 duplicate lines; I need to change the contribution codes so that there is no Pk error.

nm_Matricula    Mes_Ano_Referencia  Mes_Ano_Competencia Cod_Contribuicao    DT_pagamento
    32665107            102001             102001               11          2001-10-18
    32665107            102001             102001               11          2001-10-19
    32665107            102001             102001               11          2001-10-22
    32665107            102001             102001               11          2001-10-23



 nm_Matricula   Mes_Ano_Referencia  Mes_Ano_Competencia Cod_Contribuicao    DT_pagamento
    32665107            102001             102001               257         2001-10-18
    32665107            102001             102001               258         2001-10-19
    32665107            102001             102001               259         2001-10-22
    32665107            102001             102001               260         2001-10-23

sql

asked by anonymous 23.05.2017 / 18:23

1 answer

Spring MVC and UTF-8 insert into two tables at the same time with php [duplicate]

score 0 · Answer 1

Considering that the value of the column Cod_Contribuicao must be renumbered for each matrícula, ordered by the date of payment, here is the sketch of the code.

-- código #1 v3
with cteRecod as (
SELECT nm_Matricula, Mes_Ano_Referencia, Mes_Ano_Competencia, 
       reCod_Contribuicao= 256 + row_number() over (partition by nm_Matricula order by DT_pagamento asc)
  from tbOrigem
  -- where ...
)
INSERT into tbDestino (colunas)
  SELECT nm_Matricula, Mes_Ano_Referencia, Mes_Ano_Competencia, reCod_Contribuicao
    from cteRecod;

Replace tbOrigem and tbDestino with table names.

In the command with the INSERT statement, replace columns with the names of the columns in the destination table, in the same SELECT sequence.

If it is necessary to restrict the reading of the source table, use the WHERE clause in cteRecod.