表1上ID的字段名称，但其他表上的名称 [英] Field name from ID on table 1 but name on other table

问题描述

这是一个Firebird数据库.

This is a Firebird database.

第一张桌子

联系公司ID-职位

第二张表

Client_id-公司名称

Client_id - Co_name

在联系人中，我想在job_title字段中包含co_name.

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

client_id和company_id相同. Co_name对应于company_id和client_id.

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

此:

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')

给我一​​个错误，因为找不到(clients.co_name)

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

另一种选择:

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

给我一​​个关于JOIN的错误

gives me an error on JOIN

还有其他想法吗?

推荐答案

要从其他来源更新表，可以使用

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

可以使用UPDATE，但是由于缺乏对联合更新的支持，它会变得很丑陋，等效查询将类似于下面的代码.我不确定这是否可以在Firebird 1.5中使用.

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.

这篇关于表1上ID的字段名称，但其他表上的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！