UPDATE table1 set table1.lastname = table2.lastname where table1.ssn = table2.ssn

if both table1 and table at the first record then nothing is replaced in my little example. the record pointer of table2 is not moving. i am wanting for each record in table1 that all records in table2 are searched until an SSN match is found and then lastname is replaced. Just thought I could do it in an SQL statement.

Brenda

*******************

>
>Let's see your UPDATE statement.
>
>heers
>imN

***************
>
>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
********************
>>>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