SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
T-SQL UPDATE JOIN on a table.

10 June,2011 by Jack Vamvas

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;

 

 Read More

Filtered statistics, T-SQL best practises for T-SQL Tuesday

SQL Server – I want to become a DBA. What should I first learn ?


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

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer