Question

我试图将一个表中的键添加到另一个表，以便创建一对多关系。我想添加外键的表当前有员工的名字和姓氏，但没有ID。我想查询员工表，根据他们的名字和姓找到ID，然后将其添加到伤害表中。

我不断收到以下错误：

The multi-part identifier "InjuryOLD.FirstName" could not be bound.
The multi-part identifier "dbo.InjuryOLD.LastName" could not be bound.

下面是SQL语句：

Insert into dbo.InjuryOLD(dbo.InjuryOLD.EmpID)
Select dbo.EmployeeInformation.EmpID
From EmployeeInformation
Where exists(select dbo.EmployeeInformation.EmpID from dbo.EmployeeInformation
             where dbo.EmployeeInformation.FirstName = dbo.InjuryOLD.FirstName 
             and dbo.EmployeeInformation.LastName = dbo.InjuryOLD.LastName)

我已经确保我引用的表和列没有拼写错误，我知道两名员工有可能同名。然而，在这种情况下并非如此。

Answer 1

SELECT部分和INSERT

所以



Select dbo.EmployeeInformation.EmpID
From EmployeeInformation
Where exists(select dbo.EmployeeInformation.EmpID from dbo.EmployeeInformation
             where dbo.EmployeeInformation.FirstName = dbo.InjuryOLD.FirstName 
             and dbo.EmployeeInformation.LastName = dbo.InjuryOLD.LastName)


…不起作用，因为dbo。任何FROM子句中都没有引用InjuryOLD。你可能想把dbo放进去。Exists子查询的from子句中的inquiryOLD，而不是重复EmployeeInformation

Select dbo.EmployeeInformation.EmpID
From EmployeeInformation
Where exists(select 1 from dbo.InjuryOLD  
             where
                 dbo.EmployeeInformation.FirstName = dbo.InjuryOLD.FirstName 
                 and dbo.EmployeeInformation.LastName = dbo.InjuryOLD.LastName)

友情链接