could not update

Question

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.

we would need to see what the table definitions look like for both tables. — Thomas, Oct 28 '15 at 10:31
Is there a missing `[`? Should it read `ON [r.FileNo] =` or was this a typo on pasting the code into SO? — MiguelH, Oct 28 '15 at 10:37

score 2 · Answer 1 · edited May 23 '17 at 11:58

2

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?

edited May 23 '17 at 11:58

Community

1
1

answered Oct 28 '15 at 10:37

Darek.K

128
2
8

score 0 · Answer 2 · answered Oct 28 '15 at 10:36

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

score 0 · Accepted Answer · answered Oct 28 '15 at 10:38

0

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

answered Oct 28 '15 at 10:38

Bacon Bits

30,782
5
59
66

Thanks guy. Although i wish to make it now for all those records not only with file no 1000040000 – user1542323 Oct 28 '15 at 10:50
@user1542323 Then modify the WHERE clause appropriately? I have no idea what you're asking. – Bacon Bits Oct 28 '15 at 11:01

could not update

3 Answers3