Oracle how to select and update one field value and insert records quickly

We have the below scenario:

1. create table

   create table tt (id number, name varchar2(30), note varchar2(4));
   insert into tt values (1,'AC','m');
   insert into tt values (2,'Test','f');
   

2. we want to select the records from table tt and insert another record by updating one field value:

   INSERT INTO tt SELECT id, 'x1', note FROM tt where id=1; 
   

As our table has many columns and we would like to find one way doing the above things without listing all the columns names in the step2’s sql. Is there any way can help that?

Answer

You have many ways to get your response.

I will try to explain to you the first one which will occur me now. I wish you work in version 12c+. If that is the case:

You have a beautiful function name LISTAGG.

In addition, you have the table [DBA | ALL | SOURCE ]_TAB_COLUMNS.

*If you are not working in 12c+ you must concatenate the fields of all_tab_columns into a VARCHAR2 query like:

CURSOR my_cursor IS SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'MY_TABLE';

You can obviously order it.

...
BEGIN
  FOR cur IN my_cursor LOOP
    v_fields := v_fields || ',' cur.COLUMN_NAME;
  END LOOP;
  EXECUTE IMMEDIATE 'SELECT ' || v_fields || ' FROM MY_TABLE';
END;

I am not sure but in v_field you may remove the last comma. *

If you can Access to LISTAGG.

It is so easy.

SELECT LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID) INTO v_fields
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = <MY_TABLE>;

You can ORDER for anything, even just replace all the ORDERs parenthesis by (NULL), but not empty. Where v_field must be like VARCHAR2(2000)

Now, finally. You need some array or collection that has the list of fields which you want to modify. For example, if you have like…:

v_field := 'col1, col2, col3, col3 ... coln'

and array v_varray := myvarray('col1', 'col3', colx);

BEGIN
  FOR i IN 1..v_varray.COUNT LOOP
    v_field := REPLACE(v_field, v_varray(i), v_array(i) * <MY_MODIFICATION>)
  END LOOP;
END;

With this method, You must detect your critical fields. You must work with VARCHARS.

I wish to help you. If you need more detail contact me.

Regards.