Field name from ID on table 1 but name on other table

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?

arhey · Answer 1 · 2016-04-07T10:38:44.403

0

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

edited Apr 07 '16 at 10:38

answered Apr 07 '16 at 10:28

arhey

311
1
9

the field to change is 'Job_title' – L_GIB Apr 07 '16 at 10:35
problem is on contacts is company ID – L_GIB Apr 07 '16 at 10:35
UPDATE Contacts SET Contacts.Job_title = clients.Co_name where co_name in (select co_name from clients where clients.client_id=contacts.company_id) – L_GIB Apr 07 '16 at 10:48
For Firebird this isn't valid syntax. – Mark Rotteveel Apr 07 '16 at 16:28
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 other option: UPDATE Contacts JOIN Clients ON Clients.Client_id = Contacts.Client_id SET Contacts.Job_title = Clients.Client_name error on JOIN – L_GIB Apr 08 '16 at 10:41
Any suggestions Mark ? – L_GIB Apr 08 '16 at 10:58

Mark Rotteveel · Answer 2 · 2020-12-31T16:46:39.777

0

To update a table from another source, you can use MERGE, which only works with Firebird 2.1 or higher:

merge into Contacts 
  using Clients
  on Contacts.Company_ID = Clients.Client_id
  when matched then update set Contacts.Job_title = Clients.co_name

Using UPDATE would be possible, but it would get ugly fast because of the lack of support for joined updates, the equivalent query would be something like the code below. I'm not sure if this will work in Firebird 1.5.

update Contacts 
  set Job_title = (select Clients.co_name from Client where Clients.Client_id = Contacts.Company_ID)
  where exists (select * from Client where Clients.Client_id = Contacts.Company_ID)

This might be a bit inefficient because of the two sub-selects that are evaluated independently.

edited Dec 31 '20 at 16:46

answered Apr 08 '16 at 11:07

Mark Rotteveel

100,966
191
140
197

@LeoFazzi What is the exact error message, and what are the exact types of the columns, `co_name`, `Job_title`, `Client_id` and `Company_ID`? – Mark Rotteveel Apr 08 '16 at 11:23
@LeoFazzi What Firebird version are you using, `merge` was introduced in Firebird 2.1 – Mark Rotteveel Apr 08 '16 at 11:26
Hi Mark, the fields are correct: Table Contacts and table Clients - Columns contacts.company_id and contacts.job_title and then clients.client_id and clients.co_name. the Error: ISC error code: 335544569 ISC Error Message: Dynamic SQL error SQL error code = -104 token unknown -line 1, char 1 merge – L_GIB Apr 08 '16 at 11:29
firebird 1.5.. old I know – L_GIB Apr 08 '16 at 11:30
@LeoFazzi I'm not even sure if my alternate solution with the update will work with Firebird 1.5. You should really consider upgrading. – Mark Rotteveel Apr 08 '16 at 11:55
what error do you got with alternate solution posted by Mark? – Livius Apr 27 '16 at 10:52

Field name from ID on table 1 but name on other table

2 Answers2

Linked