update PersonInfo pi
set pi.PhoneNumber =
(select ci.PhoneNumber from ClientInfo ci
where ci.Client_ID = pi.Client_ID)
where pi.PersonType = 0
and exists (select 1 from ClientInfo ci1
where ci1.Client_ID = pi.Client_ID);

-- then add this because you don't want the same data in 2 places, do you?

alter table ClientInfo
drop PhoneNumber;

--
Alan J Davies
Aldis



Quoting Helen Borrie <helebor@...>:

> At 08:54 14/10/2008, you wrote:
>>> Insert into PersonInfo (Client_ID, PersonType, PhoneNumber) select
>>> Client_ID, 0, PhoneNumber from ClientInfo
>>
>>
>> Thanks Alexandre--but I need to clarify.
>>
>> The record in the destination table will already--I just need to move
>> the data to the existing record.
>>
>> I'm thinking I need a stored proc. Right?
>
> Not essential. You can do this with a DSQL statement and a couple
> of correlated subqueries:
>
> ClientInfo:Client_ID PhoneNumber --> PersonInfo:Client_ID,
> PersonType=0, PhoneNumber
>
> update PersonInfo pi
> set pi.PhoneNumber =
> (select ci.PhoneNumber from ClientInfo ci
> where ci.Client_ID = pi.Client_ID)
> where pi.PersonType = 0
> and exists (select 1 from ClientInfo ci1
> where ci1.Client_ID = pi.Client_ID)
>
> Note: you won't need the existence check if your PersonType = 0
> criterion is actually the way you have recorded the fact that a
> corresponding ClientInfo record exists.
>
> ./heLen
>
>