sql - 关于 SQL 查询的帮助 - 无法绑定(bind)由多部分组成的标识符

Question

我正在尝试在同一个查询中执行子选择和内部联接，但收效甚微。

下面是查询，并返回错误

The multi-part identifier "Company.LocalityId" could not be bound.

UPDATE Company
SET
    Company.BusinessRegionId = b.Id
FROM 
(
    SELECT
        Id
      FROM
        BusinessRegion
      WHERE
        tag = 'Australia/New South Wales'
) b
INNER JOIN Locality l ON 
    Company.LocalityId = l.Id
where 
    l.StateOrProvinceAbbreviation = 'NSW'

如有任何帮助，我们将不胜感激。

Answer 1

您的 BusinessRegion 和 Locality 表在该查询中根本没有真正联接，对吗？你能改成这样吗？

UPDATE Company
SET BusinessRegionId = (
    SELECT TOP 1 Id
    FROM BusinessRegion
    WHERE Tag = 'Australia/New South Wales')
FROM Locality l
WHERE l.Id = LocalityId AND l.StateOrProvinceAbbreviation = 'NSW'