sql - Field erroneously repeating in transfer from staging to final table

Question

I routinely transfer data from a staging table to a permanent table. However, I have recently discovered that one field that I'm transferring, Inst_No, repeats itself over and over in every row, regardless of the other information in that row. The data in the staging table is fine, it's after the data transfer that things get weird. For example:

---

Study Name1 Name2 Inst_no
-------------------------

 1        study1   abc       23
 2        study2   def       23
 3        study3   ghi       23

Inst_no should be different for each row, but it's not.

I'm using the following code, but can't find anything wrong with the code:

--    Insert new protocols into the table
    insert into budget_and_contracts (protocol_no, current_agreement_status, inst_no, contracts_notes, 
    team_member, creation_date, negotiation_yale, negotiation_sponsor, contracts_final, negotiation_comm, 
    protocol_id)
    select protocol_no, current_agreement_status, inst_no, contracts_notes, team_member, creation_date, 
    negotiation_yale, negotiation_sponsor, contracts_final, negotiation_comm , protocol_id
    from budget_and_contracts_staging t2
    where t2.protocol_no in (select protocol_no from budget_and_contracts_staging where protocol_no not 
    in (select protocol_no from budget_and_contracts))
    ;
    --update id_cols
    update budget_and_contracts set id_col = rownum;
    update budget_and_contracts_staging set id_col = rownum
    ;
    --update budget_and_contracts with staging data
    update budget_and_contracts t1
    set (current_agreement_status, inst_no, contracts_notes, team_member, creation_date, 
    negotiation_yale, negotiation_sponsor, contracts_final, negotiation_comm, protocol_id) = 
          (select t2.current_agreement_status, t2.inst_no, t2.contracts_notes,  t2.team_member, 
    t2.creation_date, t2.negotiation_yale, t2.negotiation_sponsor, t2.contracts_final, 
    t2.negotiation_comm, t2.protocol_id
        from budget_and_contracts_staging t2
        where t1.protocol_id = t2.protocol_id
        and t2.id_col = (select max(id_col) from budget_and_contracts_staging where protocol_id = 
    t2.protocol_id))
    where exists (select 1
              from budget_and_contracts_staging t2
              where t1.protocol_id = t2.protocol_id)
      and t1.id_col = (select max(id_col) from budget_and_contracts where protocol_id = t1.protocol_id)
    ;
    --delete duplicates (cleanup)
    delete from budget_and_contracts where id_col in (select min(id_col) from budget_and_contracts where 
    protocol_no in (select  protocol_no from budget_and_contracts_staging) group by protocol_no having 
    count(id_col)>1)
    ;
    --update id_cols for the report's use
    update budget_and_contracts set id_col = rownum;
    commit;

question from:https://stackoverflow.com/questions/65853616/field-erroneously-repeating-in-transfer-from-staging-to-final-table

Answer 1

Waitting for answers