title: How do I update a single value inside a nested table type in Oracle11g?
body: <p>My types and tables are as follows,</p>
  
  <pre><code>create type investments_type as object(
    company ref stocks_type,
    purchase_price number(12,2),
    date_purchased date,
    qty number
  );
  
  create type investments_table_type as table of investments_type;
  
  create type clients as object(
    client_name varchar(50),
    address address_type,
    investments investments_table_type
  );
  
  create table clients_table of clients
  NESTED TABLE investments STORE AS investments_table;
  </code></pre>
  
  <p>Now what I want to do is update the <code>qty</code> in the <code>investments_type</code> according to the <code>date_purchased</code>.</p>
  
  <p>I have tried doing it like like this,</p>
  
  <pre><code>update clients_table
  set investments = CAST(MULTISET(
  select investments_type(company,purchase_price,date_purchased,qty+100)
  from table(investments) i
  where i.date_purchased &lt; '01-MAR-00') as investments_table_type)
  </code></pre>
  
  <p>But this replaces all other values in the table.</p>
  
  <p>How can I only update the qty value of the rows that satisfy the where condition without replacing any other rows?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>This is how I managed to do it.</p>
  
  <pre><code>update table(select c.investments
               from clients_table c) i
  set i.qty = i.qty + 100
  where i.date_purchased &lt; '01-MAR-00';
  </code></pre>