(SQL) UPDATING a table from a join

[Quoted] [Quoted] Is there a way to perform an EFFICIENT update, where the [Quoted] [Quoted] 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".

[Quoted] 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

[Quoted] Best regards

Emmanuel Baechler
Lausanne
Switzerland Received on Thu Jun 11 1998 - 13:25:46 CEST