46UTQKMM4YE5 BlogCatalog

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;

Source:Jack Vamvas (http://www.sqlserver-dba.com)

46UTQKMM4YE5
Author: Jack Vamvas (http://www.sqlserver-dba.com)