Re: (SQL) UPDATING a table from a join
Date: Thu, 11 Jun 1998 13:03:22 GMT
Message-ID: <3581d4e8.3400609_at_192.86.155.100>
A copy of this was sent to Emmanuel Baechler <ebaechle_at_hospvd.ch> (if that email address didn't require changing) On Thu, 11 Jun 1998 13:25:46 +0200, you 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),
> .....
> )
>
[snip]
>
>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:
>
[Quoted] Have you tried something like:
update piece
set level = '02'
where rowid in ( select a.rowid
from piece A, piece_list B
where level = '04'
and listname = 'TOTO'
and piece_no = piece_number )
/
if the count(*) runs fast, then the generation of the set of rowids should be fast...
[snip]
>Best regards
>
>Emmanuel Baechler
>Lausanne
>Switzerland
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jun 11 1998 - 15:03:22 CEST