Having error on JOIN

Question

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

Possible duplicate of [Field name from ID on table 1 but name on other table](http://stackoverflow.com/questions/36473228/field-name-from-id-on-table-1-but-name-on-other-table) — Daniel A. White, Apr 08 '16 at 10:35
i would still expand it over there and discuss it with the person who submitted the answer. — Daniel A. White, Apr 08 '16 at 10:37
ok I will Daniel.. just trying to woprk out how to edit the previous :O — L_GIB, Apr 08 '16 at 10:39

score 0 · Answer 1 · edited May 23 '17 at 11:59

Possibly already answered here: Update records in one table using another table's records as WHERE parameters - looks like it should work, but I'm not a Firebird expert.

The code from that answer (but look for more context, and alternative answers):

UPDATE Table1
SET Column1 = NULL
WHERE NOT EXISTS 
    (SELECT 1 
    FROM Table2 
    WHERE Table2.Column2 = Table1.Column2)

(couldn't flag question as duplicate because that question has no upvoted or accepted answer)

Having error on JOIN

1 Answers1