Re: Update multiple fields with select

If you have two unique indexes

create unique index my_tab2_pk on my_tab2 (pk); create unique index my_tab3_pk on my_tab3 (pk);

you can also do

update (

select	t1.f1
	, t1.f2
	, t1.f3
	, t2.xyz
	, t3.foo
	, t3.bar
from	my_tab1 t1
	, my_tab2 t2
	, my_tab3 t3
where	1 = 1
and	t1.pk = t2.pk
and	t1.pk = t3.pk
and	t1.fldx = 'Anything'
) x
set	x.f1 = x.xyz
	, x.f2 = x.foo
	, x.f3 = x.bar

This technique is called "updatable view". It may be faster than Michel's good suggestion when you have big tables my_tab1 because the "lookups" in the tables my_tab2 and my_tab3 are not done in a row-by-row fashion but in a batch mode.

Martin

Michel Cadot wrote:
>
> "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
Received on Tue May 15 2001 - 17:01:20 CDT