Re: Update multiple fields with select

Exactly what I wanted. Thanks.

On Wed, 16 May 2001 00:01:20 +0200, Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de> wrote:

>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
>

EMail: knijff_at_bigfoot.com Received on Wed May 16 2001 - 14:25:22 CDT