How to run update query to read one table and update a value in a related table?

[ncsthbell]

Our business is changing the current employee UserId's to a new value. I am trying to build a query that will read the current user table, find the matching row in the new user table & update the existing userID with the value of the new userID in the new table. I want the query to run against all the rows to do the updates.

I have tried to build an 'update' query that would read the 'strUserID' from tblUsers and grab the matching 'strUserID' from the tblUsersNew and update tblUsers.strUse rID with the new userID value tblUsersNew.new UserID. Here is my query:

 
UPDATE tblUsers SET strUserID = (Select newUserID from tblUsersNew where tblUsers.strUserID = tblUsersNew.NewUserID)

I am recieving the error in the attachment.

[Rabbit]

As a rule of thumb, I don't open attachments from strangers. Please post the error message in the thread instead.

But as far as your query goes, it doesn't make sense. You are updating the user ID to the new user ID where the user ID matches the new user ID... Meaning that you're not updating anything at all because they're already the same.