CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user1542323
user1542323

Reputation: 13

could not update

update [RegistryCopy].[dbo].[RegFile_New]
SET r.[FileNo] = v.[Cont#Account (New ACC)]
where r.[FileNo] IN
    (
    SELECT r.[FileNo]
    FROM [RegistryCopy].[dbo].[RegFile_New] as R
    INNER JOIN
    [Registry_Malta_Recovered].[dbo].OLD_NEW_AccountsFromSap as v ON 
     r.FileNo] = v.[Acct in legacy (Old ACC)]
      where 
      r.[FileNo] = ('1000040000')
     )

I'm getting the error message:

The multi-part identifier "r.FileNo" could not be bound.

Upvotes: 0

Views: 32

Answers (3)

Bacon Bits
Bacon Bits

Reputation: 32220

Your statement is invalid. You're referring to aliases in the outer query that are defined in the inner query (subquery), and you're also referring to fields in the outer query for tables that are only defined in the inner query.

This is the closest approximation of your query:

UPDATE r
SET [FileNo] = v.[Cont#Account (New ACC)]
FROM [RegistryCopy].[dbo].[RegFile_New] AS R
INNER JOIN [Registry_Malta_Recovered].[dbo].OLD_NEW_AccountsFromSap AS v
    ON r.[FileNo] = v.[Acct in legacy (Old ACC)]
WHERE r.[FileNo] = ('1000040000')

Upvotes: 0

Darek.K
Darek.K

Reputation: 128

It seems that "r" and "v" identifiers are available for the sub-query only.

Please try to use UPDATE FROM syntax as it was done here: Update a table using JOIN in SQL Server?

Upvotes: 2

Raj More
Raj More

Reputation: 48048

It is complaining because you are telling it to update this table/alias called r but this r is not found in the table name above.

You can rewrite the query to be this:

UPDATE r
SET r.[FileNo] = v.[Cont#Account (New ACC)]
FROM [RegistryCopy].[dbo].[RegFile_New] as r
INNER JOIN [Registry_Malta_Recovered].[dbo].OLD_NEW_AccountsFromSap as v
    ON r.FileNo] = v.[Acct in legacy (Old ACC)]
WHERE r.[FileNo] = ('1000040000')

Upvotes: 0

Related Questions