Populate column from table X with value from table Y

Question

A fellow developer changed all the values in the userid column of table map. I need them changed back, because userid is also a key in the profiles table. Thankfully, for reasons that aren't worth going into, map and profiles share another column in common, employeeId.

So I'd like to take all the values for userid as found in profiles and overwrite the values found in userid the matching row of map.

My instinct is to do something like this:

UPDATE map,profiles
SET map.userid = profiles.userid
WHERE map.employeeId = profiles.employeeId

But SQLServer 2005 doesn't care to have two tables in the UPDATE clause.

Any suggestions?

Answer 1

In T-SQL syntax, the Update portion contains what you want to update, and the FROM may contain additional source(s) of data.

Try the following

UPDATE map
SET map.userid = profiles.userid
FROM profiles
WHERE map.employeeId = profiles.employeeId

Update Syntax: http://msdn.microsoft.com/en-us/library/ms177523.aspx

Answer 2

You can have FROM clause in the update:

UPDATE m 
SET m.userid = profiles.userid
from map m
inner join profiles on m.employeeId = profiles.employeeId