Re: A better way of achieving this query?

On 8 Apr 2002, urbanlegend_at_ozemail.com.au wrote:
> 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.

Why wouldn't the fields in TableA be on TableB?

> All I could think of was something like:
>
> Update TableA x
> Set Address =
> (Select TableB.Address from TableB TableB
> where x.ID = TableB.ID);

You could try

update
(
select address,b_address
from tablea a tableb b
where a.id = b.id
)
set address = b_address;

If you get an error containing the words "key preserved" then you will need to create a unique index on tableb.id.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.