In article <5mfnac$ojh_at_sjx-ixn7.ix.netcom.com>, breitliw_at_netcom.ca
says...
> 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.
>
The statement could be phrased as
update tbl_a set some_column = value
where exists (select some_coulmn
from tbl_a a, tbl_b b
where a.key1 = b.key1
and a.key2 = b.key2
and b.other_column = xyz)
Received on Tue Jun 03 1997 - 00:00:00 CDT
