How to change the contribution code for duplicate records

Asked

Viewed 42 times

0

I have a query that returns me 04 duplicated lines; I need to change the contribution codes so that no Pk error occurs. 

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

  • Could you post your query and format the answer better? To return user not duplicated data the DISTINCT.

  • @Cidineiaokamoto: What is the database manager? // Note that the rows are not duplicated as they differ in the Dt_payment column. // What is the rule for changing values in the Cod_contribution column? That is, as 11 turned 257,.. 260.

  • I am uitlizando sqlserver 2014, the column dt_payment I only include for ordering.. the fields I need to do Insert are just the nm_Matricula Mes_ano_reference Mes_ano_competence Cod_contribution,

  • @Cidineiaokamoto: What is the rule to change values in the Cod_contributor column? That is, how Cod_contributor=11 turned 257, 258, 259 and 260? // In the target table, which columns are part of the primary key?

  • First contribution 257, second 258 and so on.. The key of the table q will receive these records is: nm_Matricula Mes_ano_reference Mes_ano_competence Cod_contribution

1 answer

0

Considering that the value of the Cod_contribution column should be renumbered for each registration, ordered by the date of payment, here is 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 tbDestin by table names.

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

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

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.