How to update a varray type within a table with a simple update statement?

I don't believe you can update a single object's value within a varray from plain SQL, as there is no way to reference the varray index. (The link Alessandro Rossi posted seems to support this, though not necessarily for that reason). I'd be interested to be proven wrong though, of course.

I know you aren't keen on a PL/SQL approach but if you do have to then you could do this to just update that value:

declare  l_object_list my_object_varray;  cursor c is    select l.id, l.object_list, t.*    from my_object_table l,    table(l.object_list) t    where t.value1 = 10    for update of l.object_list;begin  for r in c loop    l_object_list := r.object_list;    for i in 1..l_object_list.count loop      if l_object_list(i).value1 = 10 then        l_object_list(i).value2 := 'obj 4 upd';      end if;    end loop;    update my_object_table    set object_list = l_object_list    where current of c;  end loop;end;/anonymous block completedselect l.id, t.* from my_object_table l, table(l.object_list) t;        ID     VALUE1 VALUE2         VALUE3---------- ---------- ---------- ----------         1          1 object 1           10          1          2 object 2           20          1          3 object 3           30          2         10 obj 4 upd          10          2         20 object 5           20          2         30 object 6           30 

SQL Fiddle.

If you're updating other things as well then you might prefer a function that returns the object list with the relevant value updated:

create or replace function get_updated_varray(p_object_list my_object_varray,  p_value1 number, p_new_value2 varchar2)return my_object_varray as  l_object_list my_object_varray;begin  l_object_list := p_object_list;  for i in 1..l_object_list.count loop    if l_object_list(i).value1 = p_value1 then      l_object_list(i).value2 := p_new_value2;    end if;  end loop;  return l_object_list;end;/

Then call that as part of an update; but you still can't update your in-line view directly:

update (  select l.id, l.object_list  from my_object_table l, table(l.object_list) t  where t.value1 = 10)set object_list = get_updated_varray(object_list, 10, 'obj 4 upd');SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table

You need to update based on relevant the ID(s):

update my_object_tableset object_list = get_updated_varray(object_list, 10, 'obj 4 upd')where id in (  select l.id  from my_object_table l, table(l.object_list) t  where t.value1 = 10);1 rows updated.select l.id, t.* from my_object_table l, table(l.object_list) t;        ID     VALUE1 VALUE2         VALUE3---------- ---------- ---------- ----------         1          1 object 1           10          1          2 object 2           20          1          3 object 3           30          2         10 obj 4 upd          10          2         20 object 5           20          2         30 object 6           30 

SQL Fiddle.

If you wanted to hide the complexity even further you could create a view with an instead-of trigger that calls the function:

create view my_object_view as  select l.id, t.* from my_object_table l, table(l.object_list) t/create or replace trigger my_object_view_triggerinstead of update on my_object_viewbegin  update my_object_table  set object_list = get_updated_varray(object_list, :old.value1, :new.value2)  where id = :old.id;end;/

Then the update is pretty much what you wanted, superficially at least:

update my_object_viewset value2 = 'obj 4 upd'where value1 = 10;1 rows updated.select * from my_object_view;        ID     VALUE1 VALUE2         VALUE3---------- ---------- ---------- ----------         1          1 object 1           10          1          2 object 2           20          1          3 object 3           30          2         10 obj 4 upd          10          2         20 object 5           20          2         30 object 6           30 

SQL Fiddle.

As the Oracle documentation states here

While nested tables can also be changed in a piecewise fashions, varrays cannot.

There is no way to modify VARRAYS in piecewise fashion. The only things you could do are:

• Convert the data type of your fied into a NESTED TABLE (CREATE TYPE xxx AS TABLE OF yyy)
• Fetch the varray of the row you want to change, modify with in your client language, then update the row to set the modified value on it.

Have you tried this?

UPDATE (    SELECT value2    FROM        TABLE(SELECT object_list FROM my_object_table)    WHERE value1 = 10) tSET t.value2 = 'object 4 upd';

I couldn't test this query, use with care. I'm not sure Oracle can actually do that...