如何在Oracle11g中的嵌套表类型内更新单个值? [英] How do I update a single value inside a nested table type in Oracle11g?

问题描述

我的类型和表格如下，

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;

现在我要做的是根据date_purchased更新investments_type中的qty.

Now what I want to do is update the qty in the investments_type according to the date_purchased.

我已经尝试过像这样

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 < '01-MAR-00') as investments_table_type)

但这将替换表中的所有其他值.

But this replaces all other values in the table.

如何只更新满足where条件的行的数量值，而不替换其他行?

How can I only update the qty value of the rows that satisfy the where condition without replacing any other rows?

编辑

这就是我设法做到的方式.

This is how I managed to do it.

update table(select c.investments
             from clients_table c) i
set i.qty = i.qty + 100
where i.date_purchased < '01-MAR-00';

推荐答案

您可以将对象类型转换为表并更新该表.看起来像这样(未经测试):

You can transform your object type to a table and update this one. It looks like this (not tested):

update 
    (select purchase_price, date_purchased, qty 
    from clients_table 
    natural join table(investments) 
    where date_purchased < '01-MAR-00')
set qty = qty + 100;

从我这边得到的小提示:当您想了解Oracle对象类型时，请从更简单的结构开始，而不要从对象表对象"开始.

Small hint from my side: When you want to learn about Oracle Object Types, start with simpler structs, not "Object of Object of Table of Object"

这篇关于如何在Oracle11g中的嵌套表类型内更新单个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！