title: Oracle - Copy data from one row to another based on dynamic selected columns
body: <p>I have a table with two rows and I need to copy some data in row A to row B.
  My biggest concern here is that the columns involved are not static in name or number (table can grow or shrink in column number).</p>
  
  <p>I came up with a solution that i don't like at all...</p>
  
  <pre class="lang-sql prettyprint-override"><code>create or replace
  PROCEDURE Z_COPY_TASK_ATT_PE
  (
    par_oldRowId IN VARCHAR2,
    par_newRowId IN VARCHAR2
  )
  IS
    var_update VARCHAR2(4000 BYTE);
    var_crr_col_value_old VARCHAR2(10 BYTE);
    var_crr_select VARCHAR2(4000 BYTE);
  BEGIN
  
    var_update := 'UPDATE PLANNING_ENTITY SET ';
  
    for i in (
              Select COLUMN_NAME 
              from user_tab_columns 
              where table_name='PLANNING_ENTITY' 
              and lower(COLUMN_NAME) like 'code%'
              )
    loop
  
      var_crr_select := 'select ' || i.column_name 
        || ' from planning_entity where planning_code = ''' 
        || par_oldRowId || ''''; 
  
      execute immediate var_crr_select
        into var_crr_col_value_old;
  
      var_update := var_update || i.column_name || ' = ''' || var_crr_col_value_old || ''', ';
    end loop;
  
    var_update := SUBSTR(var_update, 0, length(var_update)-2 );
    var_update := var_update || ' where planning_code = ''' || par_newRowId || '''';
  
    execute immediate var_update;
    commit;
  END;
  </code></pre>
  
  <p>My major issue here (for me at least) is the fact that i need to make the select for each iteration of the loop. It would be really nice if i could make a select that returned something like column|name and then all I needed to do was i.column and i.value.</p>
  
  <p>What do you think?</p>
  
  <p>===== Solution following Marmite Bomber Answer =====
  </p>
  
  <pre><code>create or replace
  PROCEDURE Z_COPY_TASK_ATT_PE
  (
    par_oldRowId IN VARCHAR2,
    par_newRowId IN VARCHAR2
  )
  IS
    var_update VARCHAR2(4000 BYTE);
    var_columns varchar2(4000 BYTE);
  BEGIN
  
    Select LISTAGG(COLUMN_NAME, ',') WITHIN group (order by column_name) into var_columns
    from user_tab_columns 
    where table_name='PLANNING_ENTITY' 
    and lower(COLUMN_NAME) like 'code%';
  
    var_update := 'UPDATE PLANNING_ENTITY PE1 SET (' || var_columns ||
      ') = (SELECT ' || var_columns || ' FROM PLANNING_ENTITY PE2 WHERE PE2.PLANNING_CODE = ''' || par_oldRowId ||
      ''') WHERE PE1.PLANNING_CODE = ''' || par_newRowId || '''';
  
    execute immediate var_update;
    commit;
  END;
  </code></pre>