UPDATE problem

From: Ralf-Henning Glomb <glomb_at_ralf-henning-glomb.de>
Date: Thu, 08 Nov 2001 22:29:02 +0100
Message-ID: <3BEAF91E.DB0F7175@ralf-henning-glomb.de>

Hi,

i'm looking for a hint, how to write a update-statement for the following problem.

I have table A filled with data.
Now i want to update one attrbute (attrib_dest). The source is attrib_source from another table (tab2). The association between the two

tables is tab1.key and tab2.key (both primary keys).

The problem is, that i only want to update the rows on tab1 where the corresponding value of the source table is not NULL.

So, teh following statement works, but it is much too slow.

UPDATE tab1 a
SET a.attrib_dest =

    ( SELECT b.attrib_source

        FROM  tab2 b
        WHERE a.key  = b.key
     )

WHERE tab1.key IN

    ( SELECT c.key

       FROM     tab1 c, tab2 d
       WHERE   c.key  = d.key
             AND  d.attrib_source IS NOT NULL

  )
;

It would be great if you could give me a hint how to write this statement in a better way.

Thank you very much

Ralf-Henning Received on Thu Nov 08 2001 - 15:29:02 CST