Re: (SQL) UPDATING a table from a join

From: Peter H. Larsen <plarsen_at_dc.dynares.com>
Date: Thu, 11 Jun 1998 11:16:12 -0400
Message-ID: <357FF4BC.A6E39A87_at_dc.dynares.com>


Hi,
[Quoted] Try to use EXSIST instead of the IN clause. That should give you almost the same speed as a JOIN. (it's known as a SEMI join).

  • Peter H. Larsen Oracle Consultant

Emmanuel Baechler wrote:

> Hi,
>
> Is there a way to perform an EFFICIENT update, where the
> selection of rows to update depends on a join?
>
> I have two tables of the following style:
>
> CREATE OR REPLACE TABLE PIECE
> (....
> PIECE_NUMBER VARCHAR2(10),
> LEVEL VARCAHR2(2),
> ....
> )
>
> CREATE OR REPLACE TABLE PIECE_LIST
> (....
> LISTNAME VARCHAR2(10),
> PIECE_NO VARCHAR2(10),
> .....
> )
>
> The two real tables are part of a package and I am NOT
> allowed to alter them. The first one contains several
> hundreds of thousands elements. The second one may be
> of a similar size.
>
> I would like to update the field "LEVEL" to the value "02"
> for all rows such that the belong to the listname "TOTO" and
> such that their field "LEVEL" currently contain the value "04".
>
> Counting the number of relevant occurences is very easy and fast:
> SELECT COUNT (*) FROM PIECE, PIECE_LIST
> WHERE ....
> LEVEL='04' AND
> ....
> LISTNAME='TOTO' AND
> PIECE_NO, PIECE_NUMBER;
>
> But I cannot specify a join for the update. The only way I found
> for doing it is:
>
> UPDATE PIECE
> SET LEVEL='02'
> WHERE
> ...
> LEVEL='04' AND
> PIECE_NUMBER IN (SELECT PIECE_NO FROM PIECE_LIST WHERE .... AND
> LISTNAME='TOTO');
>
> Needless to say, this request is abysmally slow! Althouhg the number of
> rows requiring update is under 200, it takes several hours to run. But
> I did not found a better way to do it. I tried to create a few uncluding
> the join between the two tables. the update was refused because the view
> was not "a key preserving table" or something like that.
>
> Does anyboday have an idea to make a faster UPDATE?
>
> Any help will be appreciated
>
> Best regards
>
> Emmanuel Baechler
> Lausanne
> Switzerland
Received on Thu Jun 11 1998 - 17:16:12 CEST

Original text of this message