Re: Update multiple fields with select

"Ted Knijff" <knijff_at_bigfoot.com> a écrit dans le message news: 3b000ddf.7118015_at_news.online.de...
> What is the best way to update a table with multiple values from
> another table, such as :
>
> update my_tab1 set
> f1 = (select xyz from my_tab2 where my_tab1.pk=my_tab2.pk),
> (f2,f3) = select (foo,bar from my_tab3 where my_tab1.pk=my_tab3.pk)
> where my_tab1.fldx='Anything'
>
> I looked at the documentation, but it is not very clear to me.
>
> Any tips ?
>
> Ted
> EMail: knijff_at_bigfoot.com

Just try it:

v815> create table my_tab1 (pk number, f1 number, f2 number, f3 number, fldx varchar2(20));

Table created.

v815> create table my_tab2 (pk number, xyz number);

Table created.

v815> create table my_tab3 (pk number, foo number, bar number);

Table created.

v815> update my_tab1 set
  2 f1 = (select xyz from my_tab2 where my_tab1.pk=my_tab2.pk),   3 (f2,f3) = (select foo,bar from my_tab3 where my_tab1.pk=my_tab3.pk)   4 where my_tab1.fldx='Anything'
  5 /

0 rows updated.

--
Regards
Michel