UPDATE Temp3 SET ClientName = NVL(ALLTRIM(Client.ClientId) + " - " + Client.Cl_Name, Temp3.ClientName),;
                 InvNo	    = NVL(Commits.InvoiceId, NVL(Commits_Hist.InvoiceId, Temp3.InvNo));
FROM Temp3;
LEFT JOIN Client       ON Temp3.ClientId = Client.ClientId;
LEFT JOIN Commits      ON Temp3.DtId     = Client.DtId;
LEFT JOIN Commits_Hist ON Temp3.DtId     = Commits_Hist.DtId

But you should test this very carefully :o)



>Ok, here's the code I'm trying to replace with the SQL UPDATE:
>
>

>SELECT Temp3
>SCAN
>
>  =SEEK(TempJoin.ClientId, "Client", "ClientId")
>
>  REPLACE ClientName WITH ALLTRIM(Client.ClientId) + " - " + Client.Cl_Name NEXT 1
>
>  IF SEEK(TempJoin.DtId, "Commits", "DtId")
>    REPLACE IN TempJoin InvNo	WITH Commits.InvoiceId NEXT 1
>  ELSE
>    IF SEEK(TempJoin.DtId, "Commits_Hist", "DtId")
>      REPLACE IN TempJoin InvNo	WITH Commits_Hist.InvoiceId NEXT 1
>    ENDIF				
>  ENDIF
>
>ENDSCAN
>

>
>Commits and Commits_Hist have identical structures. I now need to include all 3 tables in the
>update: Clients, Commits and Commits_Hist.
>
>Can you help please?
>
>
>
>>:o))
>>

>>UPDATE Temp3 SET ClientName = ALLTRIM(Client.ClientId) + " - " + Client.Cl_Name
>>       WHERE Temp3.ClientId = Client.ClientId
>>
>>** or
>>UPDATE Temp3 SET ClientName = ALLTRIM(Client.ClientId) + " - " + Client.Cl_Name
>>       FROM Temp3 INNERT JOIN Client ON Temp3.ClientId = Client.ClientId
>>
>>** or if you want all fields in table3 to be updated, no metter if there are macthing records in Client (i doubt about it)
>>UPDATE Temp3 SET ClientName = NVL(ALLTRIM(Client.ClientId) + " - " + Client.Cl_Name, '')
>>       FROM Temp3 LEFT JOIN Client ON Temp3.ClientId = Client.ClientId
>>
>>

>>
>>
>>
>>>I'm using VFP9.
>>>
>>>Corrected:
>>>
>>>

>>>SELECT Temp3
>>>SCAN
>>>				
>>>    =SEEK(Temp3.ClientId, "Client", "ClientId")
>>>
>>>    REPLACE IN Temp3 ;
>>>       ClientName WITH ALLTRIM(Client.ClientId) + " - " + Client.Cl_Name
>>>       NEXT 1
>>>
>>>END SCAN
>>>

>>>
>>>
>>>>>How can I use a JOIN to update instead of:
>>>>>
>>>>>

>>>>>SELECT Temp3
>>>>>SCAN
>>>>>				
>>>>>    **=SEEK(TempJoin.ClientId, "Client", "ClientId")
>>>>>    =SEEK(Temp3ClientId, "Client", "ClientId")
>>>>>
>>>>>    REPLACE IN Temp3 ;
>>>>>       ClientName WITH ALLTRIM(Client.ClientId) + " - " + Client.Cl_Name
>>>>>       NEXT 1
>>>>>
>>>>>END SCAN
>>>>>

>>>>
>>>>Hey, from where TempJoin comes from?
>>>>BTW what version of VFP you use?