Re: (SQL) UPDATING a table from a join

From: James Lorenzen <lorenzen_at_visi.com>
Date: Thu, 11 Jun 1998 15:24:24 GMT
Message-ID: <IESf1.751$bj2.3763397_at_ptah.visi.com>

Emmanuel;
[Quoted] The "best" way to do this would be to use PL*SQL. This works only if you have the procedural option installed. The join would be performed by the cursor and the update is performed in the PL*SQL block. The performance of this will depend on the presence (or absence) of appropriate indexes.

Note: I will use the Oracle pseudo column "rowid", this will aid in performance, but it is Oracle specific.

DECLARE
  CURSOR join_csr IS

     SELECT rowid update_row
        FROM piece, piece_list
        WHERE piece.piece_number = piece_list.piece_no
           AND piece_list.listname = 'TOTO'
           AND piece.level = '04' ;

BEGIN
  FOR join_rec IN join_csr LOOP

     UPDATE piece SET level = '02'
        WHERE rowid = join_rec.update_row ;

  END LOOP ;
END ; Of course, if you do not have access to PL*SQL, this is moot.

HTH
        James

Emmanuel Baechler (ebaechle_at_hospvd.ch) 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:24:24 CEST