How to update/insert values in nested table of values while preserving existing values

Question

In our Oracle 11g database, I have a custom data type:

num_list

create or replace type
    num_list
as
    table of varchar2(25);

I then created a table that uses this datatype:

create table num_list_table(
    id number(*,0)
    ,numbers num_list
) nested table numbers store as numbers_tab
;

I then inserted the following rows:

insert into num_list_table values (1, num_list('123', 456'));

And I now have 1 row. I'm trying to figure out how to insert more values into the num_list of row 1 while preserving any existing values and without having to manually type those existing values; this is a small example of a much larger task that will require mass updates while preserving vals).

I get close with this:

update
    num_list_table
set numbers = (select num_list(numbers) from (
    select listagg(numbers, ',') within group (order by numbers) numbers
    from (select t.column_value numbers
          from 
              num_list_table nlt,
              table(nlt.numbers) t
           where
               st.id = 1
         union
         select '789'
         from dual)))
     where id = 1;

However, these results are a single entry:

num_list('123,456,789')

I need them to be multiple entries in the num_list:

num_list('123', '456', '789')

Any and all insight would be greatly appreciated.

Answer 1

You could insert a single value into nested table to a specific ID, like this:

insert into table 
       (select numbers 
          from num_list_table 
          where id = 1)
values ('123')

or using insert select:

insert into table 
       (select numbers 
          from num_list_table 
         where id = 1)
select n.same_type_field
  from another_table n;