Skip to Main Content
  • Questions
  • Update a nested column with a database tool like Oracle sql developer

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Vannessa.

Asked: March 16, 2017 - 4:03 pm UTC

Last updated: March 16, 2017 - 5:33 pm UTC

Version: Oracle12c

Viewed 1000+ times

You Asked

Hello ,
i have another question about nested tables.
When i created the table projects with the column project_name and categories. categories is the nested column.
Can i easy update the column categories (viz : i write direct in the column using a database tools such oracle SQL developer).
Are the new values automatically saved.
e.g: by project_name a , I want to change the subcat 4 into subcat 5 ant his priority 2 into priority 3.

There is a way to have predefined list of values in a nested tables that i can use late.
e.g : when i created a nested tables in a nested blocks i can initialize it. With my approach, it is feasible?

with LiveSQL Test Case:

and Chris said...

Just like selecting data, you can use the table operator to update "rows" of your nested table:

CREATE TYPE Categories AS OBJECT( 
priority VARCHAR2(10), 
subcategory VARCHAR2(60) 
);
/

CREATE TYPE CategoriesList AS TABLE OF Categories;
/

CREATE TABLE projekt ( 
projektname VARCHAR2(10), 
categories CategoriesList) 
NESTED TABLE categories STORE AS categories_tab;

INSERT INTO projekt VALUES('a',CategoriesList(Categories('1','subcat1'), 
                                              Categories('2','subcat4'), 
                                              Categories('3','subcat5')));

INSERT INTO projekt VALUES('b',CategoriesList(Categories('1','subcat1'), 
                                              Categories('2','subcat3'), 
                                              Categories('3','subcat5')));

INSERT INTO projekt VALUES('c',CategoriesList(Categories('1','subcat1'), 
                                              Categories('2','subcat2')));

commit;

update table(
  select categories
  from   projekt 
  where  projektname = 'a'
)
set   subcategory = 'TEST'
where priority = 1;

select c.* 
from   projekt p,
       table(p.categories) c;

PRIORITY  SUBCATEGORY  
1         TEST         
2         subcat4      
3         subcat5      
1         subcat1      
2         subcat3      
3         subcat5      
1         subcat1      
2         subcat2  


A good example of why I don't like storing nested tables: they make all your SQL more complicated!

Is this answer out of date? If it is, please let us know via a Comment