1 Reply Latest reply: Sep 6, 2017 2:36 AM by Marcus Sommer RSS

    Using a script to update a table

    Stephen Naude



      I have 3 tables:


      table A contains the Call data for refunds,main columns are:

      Call_ID, ID_Number


      Table B contains transaction data where a CALL_ID was written into the data, Main Columns are:

      Acc_Number, Call_ID, ID_Number


      Table C contains transaction data where CALL_ID was NOT written to the Data, Main Columns are:

      Acc_Number, ID_Number


      Table C will inherently have duplicated data that is already in Table B.


      My thinking was to use Table A to Update table C and bring the CALL_ID in, and then load it back to Table B (Concatenated). End Result is only have Table A and Table B, Table C gone.


      Problem is that i have no idea how to pull this off.


      Can anyone assist please.

        • Re: Using a script to update a table
          Marcus Sommer

          I would probably do the following:



          mapping load ID_Number, Call_ID from TableA;




               Acc_Number, Call_ID, ID_Number,

               Acc_Number & '|' & Call_ID& '|' & ID_Number as Key

          from TableB;



               Acc_Number, ID_Number,

               applymap('MapAtoC', ID_Number, '#NV') as ID_Number

          from TableC

          where not exists(Key, Acc_Number & '|' & Call_ID& '|' & applymap('MapAtoC', ID_Number, '#NV');


          and removing the tables A + C. If you want to keep A you will need a combined key to avoid synthetic key - also you might want to handle possible duplicates in a different way - its just a suggestion how you could approach to this topic.


          - Marcus