Re: tricky sql question

Don't concat your key1 and key2.

Pleae try the following SQL statement:

update tbl_a set some_column = value
where exists ( select * from tbl_b where tbl_b.key1 is not null and tbl_b.key2 is not null and tbl_b.key1 = tbl_a.key1 and tbl_b.key2 = tbl_a.key2 ) ;

Of course, you need explain plan to test the execution path.

Wolfgang Breitling <breitliw_at_netcom.ca> wrote in article <5mfnac$ojh_at_sjx-ixn7.ix.netcom.com>...
> 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 )
>
>
> on a select I could do a join:
>
> select some_column
> from tbl_a a, tbl_b b
> where a.key1 = b.key1
> and a.key2 = b.key2
> and b.other_column = xyz
>
>
> key1 and key2 are not necessarily keys for tbl_b, they just match in type
 the
> prime key columns of tbl_a.
>
Received on Wed May 28 1997 - 00:00:00 CDT