Assistance on a SQL Query - The multi-part identifier could not be bound

Question

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.

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.

Answer 1

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

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'

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