I need to ask again. this time with more details : This is a Firebird database. 

First Table

Contacts
Company_ID - job_title

Second Table

Client_id - Co_name

In contacts, I want to the job_title field to contain the co_name. 

client_id and company_id are the same. 
Co_name correspond to company_id as well as client_id. 

this:
UPDATE Contacts
SET Contacts.Job_title = Clients.co_name
where company_id in
(select client_id from clients
JOIN Contacts c ON Client_id=company_id
where record_status='A')

gives me an error as cannot find (clients.co_name) 

this other option:


UPDATE Contacts
JOIN Clients ON Clients.Client_id = Contacts.Client_id
SET Contacts.Job_title = Clients.Client_name

gives me an error on JOIN

Any other ideas please? 

Thank you all