Re: value function in SQL does not work

In article <d938f4e5.0107050833.19fea061_at_posting.google.com>, tom_xu_at_msn.com says...
>
>Hello All,
> I refer to the Oracle 8.1.7 SQL Reference documentation. There is
>a sample showing how to using SET VALUE in update: ( on page 1106)
>
>update table1 p set value(p) = (select value(q) from table2 q where
>p.id = q.id) where p.id = 10;
>
>I accormendated this syntax to my real table name in sql*plus, but it
>complains that p and q are invalid column name. (in my case, the id is
>the primary key of both table, and table1 and table2 have the same
>structure)
>
>I put the SQL into a stored procedure , but the stored procedure
>compiled with error.
>
>How can I use this sytax? Is there any standard SQL can do the same
>thing?(the difficult part is that I need to update all the columns in
>table1)

you need to be using OBJECT TYPES and TABLES of object types for the "value", "ref", and "deref" functions to make sense.

you need to do a column by column assignment with "regular" relational tables.

update t1 set ( a,b,c ) = ( select d,e,f from t2 where t2.id = t1.id ) where id = 10;

>

>thanks for any help!
>
>Tom

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp