How can I update multiple rows (bulk) from different table at nested table column?

Question

I found many examples showing how to create table with nested table column and how to update them “manually” like detailed at steps 1-5.

What I want to do: update the nested table column (“cust_info” at the example) directly/automatically from other table in the database (NEW_TBL- that has the same structure) and not entering the values one after another manually.

(1) Create Object:

CREATE TYPE TEST_OBJECT_T AS OBJECT
(
x   NUMBER,
y   NUMBER 
);

(2) CREATE collection:

CREATE  TYPE TEST_OBJ_TBL IS TABLE OF TEST_OBJECT_T;

(3) Create table with nested table column

create table aaaTable
 (
 CUSTID number,
 cust_info TEST_OBJ_TBL
 )
 NESTED TABLE cust_info STORE AS xx_tbl
 ;

(4) --insert data

insert into AAATABLE  
 VALUES (1,TEST_OBJ_TBL(
                             TEST_OBJECT_T(33,77),
                             TEST_OBJECT_T(66,67),
                             TEST_OBJECT_T(320,999)
                            )
        );

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

insert into AAATABLE  
 VALUES (2,TEST_OBJ_TBL(
                             TEST_OBJECT_T(444,555),
                             TEST_OBJECT_T(333,67),
                             TEST_OBJECT_T(111,978)
                            )
        );

(5) query 2 see results

 select T1.custid ,T2.*
  from AAATABLE T1 , table(T1. CUST_INFO) T2;

results :
CUSTID          X          Y
---------- ---------- ----------
         1         33         77 
         1         66         67 
         1        320        999 
         2        444        555 
         2        333         67 
         2        111        978

(6) Create new table, which will be the data source for updating the nested table column)

create table new_tbl
(
X1  NUMBER,
Y1  NUMBER 
);

(7) insert data into new table

insert into NEW_TBL values (222,444);
insert into NEW_TBL values (345,777);
insert into NEW_TBL values (867,222);

+++++++++++++++++++++++++++++++++

select * from NEW_TBL


   X 1         Y1
---------- ----------
       222        444 
       345        777 
       867        222 

The question again: can I insert “new_tbl” data into nested table column (“cust_info “) at bulk (meaning - replace the data at stage 4 TEST_OBJECT_T(33,77), TEST_OBJECT_T(66,67), TEST_OBJECT_T(320,999) ) I tried to use bulk collect but didn’t successes .

Thanks keren

Answer 1

You can use the COLLECT function (with CAST)

update aaatable d
set d.cust_info = (select cast ( collect(TEST_OBJECT_T(x1,y1)) as TEST_OBJ_TBL ) from new_tbl)
where d.custid = 1;

Here is a sqlfiddle demo