How can i update a column from a nested table in pl/sql? [duplicate]

Question

I'm trying to make a column in a table that can store multiple values like this.

I have an student that has an id_std and a column named marks that can take several values like 2,3,4. I want to update this list to add another mark to make 2,3,4,5 but I don't know how.

How i can update the column marks to add a new mark without erasing the previous values? Here is my code:

CREATE OR REPLACE TYPE NumberList IS TABLE OF number(5);
/
DROP TABLE test;

CREATE TABLE test ( 
   id_std int
   , marks NumberList )
Nested table marks store as numere_necesare;

insert into test(id_std, marks )
values (1,NumberList(6,7,8));

Answer 1

The syntax is a bit obscure, but basically you need to manipulate the nested table with the MULTISET operator.

update test 
set marks = marks multiset union all numberlist(42) 
where id_std = 1 

This concatenates the existing set of marks with a new set (in this case a set of one). Here is a LiveSQL demo (free Oracle Technet account required).

The advantage of this approach is that it's easy to add multiple values at once:

update test 
set marks = marks multiset union all numberlist(23, 69) 
where id_std = 1 

Nested table columns are clever in principle but awkward in practice. Using them in SQL statements can lead to some ugly SQL. If you're going to use them you will need to familiarise yourself with all the MULTISET operators. Find out more.

Answer 2

Can try this:

insert into table( select marks from test where id_std = 1 ) values ( 5 );