Are you sure that index on ID field of table B is used ?
What does EXPLAIN PLAN say ?
urbanlegend_at_ozemail.com.au (Lucas Wells) wrote in message news:<c1919f3f.0204082051.6f789b53_at_posting.google.com>...
> Hi All,
>
> Would love to know if I can achieve the following in a more efficent
> way.
>
> I have two tables, 1 of approx 300k records (TableA), the other of
> about 14 million records (TableB).
>
> TableA has a blank field (for Address values) that I want populated
> from the records in TableB.
>
> There is a 1-to-1 relationship on an ID field in each Table.
>
> All I could think of was something like:
>
> Update TableA x
> Set Address =
> (Select TableB.Address from TableB TableB
> where x.ID = TableB.ID);
>
> This takes a long time (presumably because it's performing the
> subquery 300k times?) and was wondering if I could achieve it in a
> more efficient way. Both ID fields are indexed, if that's important.
>
>
> Any help appreciated!
>
> Regards,
>
> LW
Received on Tue Apr 09 2002 - 09:38:39 CDT
