The multi-part identifier "S.clientid" could not be bound

Question

SET IDENTITY_INSERT SurveyInformations ON

UPDATE SurveyInformations 
SET SurveyInformations.id = Master_Client_Update.id
WHERE Master_Client_Update.Client_Id = SurveyInformations.clientid 
  AND Master_Client_Update.emp_id = SurveyInformations.EmployeeId 

SET IDENTITY_INSERT SurveyInformations OFF

I'm trying to copy the values from the column id in table (master_client_update) to column id in table SurveyInformations.

But I get these errors:

Msg 4104, Level 16, State 1, Line 18
The multi-part identifier "Master_Client_Update.Client_Id" could not be bound.

Msg 4104, Level 16, State 1, Line 18
The multi-part identifier "Master_Client_Update.emp_id" could not be bound.

Thanks

Answer 1

This error means that Master_Client_Update is not known to the query. You are naming the table you want to update but are not stating the source of the data to be used. Just using the name of the table in SET and WHERE clauses is not the way to go.

As for your options, you have multiple:

1. use UPDATE FROM - assuming there'll be only updates needed this might be the best option to go with:

UPDATE t
  SET t.id = s.id
FROM SurveyInformations AS t
INNER JOIN Master_Client_Update AS s
ON s.Client_Id=t.clientid AND s.emp_id=t.EmployeeId;

2. use MERGE - this might me unnecessary if the only thing you're doing is updating rows, but if by chance you want to also insert new rows this variant offers a concise way to write it - be aware though that you should know what are the possible complications (but that's true even for option 1 - see the note below):

merge SurveyInformations as t
using (select * from Master_Client_Update) as s
on s.Client_Id=t.clientid AND s.emp_id=t.EmployeeId
when matched then
update
set id = s.id;

Note:

If the statements should end up with multiple rows that could be the sources of the update, then the results are undefined, see the following for an answer, how to resolve that.