协助SQL查询 - 无法绑定多部分标识符

I'm trying to do a sub-select and Inner Join in the same query, with little success.

我试图在同一个查询中进行子选择和内连接,但收效甚微。

The query, as it stands is below, and returning the error

查询,如下所示,并返回错误

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

无法绑定多部分标识符“Company.LocalityId”。

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'

Any assistance would be gratefully received.

我们将非常感激地提供任何帮助。

2 个解决方案

#1

Your BusinessRegion and Locality tables aren't really joined at all in that query, right? Can you do something like this instead?

您的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'

#2

Here's a variation of the query:

以下是查询的变体:

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

#1