Re: tricky sql question

From: Ken <ken_at_fifthd.ca>
Date: 1997/05/29
Message-ID: <338ddaaa.2253272178@news.sas.ab.ca>#1/1

On Wed, 28 May 1997 00:25:00 GMT, tkyte_at_us.oracle.com (Thomas Kyte) wrote:

>On Tue, 27 May 1997 22:30:33 GMT, breitliw_at_netcom.ca (Wolfgang Breitling) wrote:
>
>>Does anyone know a nifty way of writing an update sql where the qualifying
>>rows are determined by matching the multicolumn prime key with values
>>returned from a subselect. This is one way, but it this way oracle doesn't use
>>any indexes:
>>
>>update tbl_a set some_column = value
>>where key1 || key2 in (select key1 || key2 from tbl_b
>>where other_column = xyz )
>>
>

how about

update tbl_a set some_column = value
where exists
(select 'x' from tbl_b

   where other_column = xyz 
   and     tbl_a.key1 = tbl_b.key1
   and     tbl_a.key2 = tbl_b.key2

)
Ken.
/ Received on Thu May 29 1997 - 00:00:00 CDT