SQL Server T-SQL UPDATE with JOIN on a table – is a simple task. Follow the pattern below. The example below takes values from one table and updates on another table – based on the JOIN between the two columns

--create #temp1_guitarists , with missing values on song column
CREATE TABLE #temp1_guitarists
(guitarist_ID INT,
 guitarist_name VARCHAR(50),
 song VARCHAR(50)
);

--create #temp1_guitarists_song  , with songs
CREATE TABLE #temp1_guitarists_song
(guitarist_ID INT,
 song VARCHAR(50)
);

 --insert values
INSERT INTO #temp1_guitarists
SELECT 1,'Joe Satriani','' UNION
SELECT 2,'Jimi Hendrix','' UNION
SELECT 3,'Prince',''; 

INSERT INTO #temp1_guitarists_song
SELECT 1,'Satch Boogie' UNION
SELECT 2,'Red House' UNION
SELECT 3,'When Doves Cry';

--Now , for the UPDATE statement
UPDATE #temp1_guitarists SET song = B.song
FROM #temp1_guitarists AS A
INNER JOIN #temp1_guitarists_song AS B
ON A.guitarist_ID = B.guitarist_ID;

--display the changes
SELECT guitarist_ID,guitarist_name,song 
FROM #temp1_guitarists;

--clean up temp tables
DROP TABLE  #temp1_guitarists;
DROP TABLE #temp1_guitarists_song;

Republished from http://www.sqlserver-dba.com.