Re: SQL Expert Question

From: Lothar Armbrüster <lothar.armbruester_at_rheingau.netsurf.de>
Date: 03 Nov 99 21:46:37 +0100
Message-ID: <1295.976T1895T13063770@rheingau.netsurf.de>

Joao Ricardo B Oliveira wrote at 03-Nov-99 13:20:15 on the subject SQL Expert Question:

>Hi there:

> Anyone can tell me what's the fastest way to UPDATE UpdA from table
> A,
>with the data from Field UpdB from Table B.
> The two tables are almost equal and the inner join columns are Id1,
> Id2,
>Id3, but in the Table B the primary key is a Sequence column.

> If i create an index (Id1, Id2, Id3) im table B it will be faster
> isnt
>it ?

> How can i use the INDEX in the UPDATE sentence ?

> Table A
> --------

> Id1 *
> Id2 *
> Id3 *
> F1
> F2
> UpdA

> Table B
> --------

> Id1
> Id2
> Id3
> F2
> UpdB

Hello Joao Ricardo,

if you have a unique index on Table B (id1,id2,id3) you can do the following:

update

   (select a.*,b.updb
    from

       table_a a,
       table_b b
    where
       a.id1=b.id1 and
       a.id2=b.id2 and
       a.id3=b.id3)

set upda=updb;

You need the unique index on table b for table a being key preserved in the above inline view. That is the key of table a must be the key of the join.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Hauptstr. 26            | lothar.armbruester_at_t-online.de
D-65346 Eltville        |

Received on Wed Nov 03 1999 - 14:46:37 CST