>>table1.costctr needs to be converted to a new value. If have put the pseudo code below but would like to do it in an SQL statement.
>>
>>if table1.costctr = costctr.o_costctr and
>> control.site_id = costctr.site_id
>>replace table1.costctr with costctr.n_costctr
>>endif
>>

>UPDATE Table1 SET CostCtr = CostCtr.n_CostCtr ;
>WHERE Table1.CostCtr = CostCtr.o_costctr ;
>AND Control.site_id = CostCtr.site_id
>
>However, it's important to understand that that works with the values of the >current records in CostCtr and Control. It's not like XBase code where you can >SET RELATION and assume the tables stay in synch as you move.
>
>Tamar

The UPDATE is not working as I would like. For a different example. Table1 has SSN and LastName. Table2 has SSN and LastName and is used to update LastName in several tables. Not all the records in Table1 will be in Table2 and not all the records in Table2 will be in Table1. If Table1.SSN = Table2.SSN and Table1.LastName = Table2.LastName I want to replace Table1.LastName with Table2.LastName.

Table1
------
1234567Smith
2222222Jones

Table2
-------
1111111Anderson
1234567Wilson

I want to replace Smith in Table1 with Wilson. But my UPDATE WHERE compares only the values in the current record. It does not move through the table to compare for each record.

Will an UPDATE work for me or should I just do it programmatically.

Brenda