MS SQL Server 2008 – How to update value using SELECT results

by

The following T-SQL shows how to update a value using the results of a SELECT query.

This applies to Microsoft SQL server (T-SQL) — and will not work in MySQL.

To do this you need a way to match the source row with the destination row, for this example we have two tables – T_People and T_Artworks – we’ll be matching the two tables using the Name field from T_People to the Artist field in T_Artworks – then setting the unique identifier from T_People to a field in T_Artworks.

For example, if we have

T_People

ID Name
1 James Bloggs
2 Jenny Campbell
3 Tim Tam

T_Artworks

ID Artist Person_ID
7 James Bloggs  
8 Jenny Campbell  
9 Tim Tam  

We want to update T_Artworks.Person_ID with T_Persons.ID by matching T_Artworks.Artist with T_Persons_Name

Always test UPDATE statements in a non-production database! Bulk changes can become bulk mistakes very quickly and can be difficult to undo.

 

See it in action on SQL Fiddle: http://sqlfiddle.com/#!3/aa579/2

UPDATE T_Artworks
SET T_Artworks.Person_ID = t1.ID
FROM ( SELECT DISTINCT ID, Name
 FROM T_Persons 
 ) t1
WHERE T_Artworks.Artist = t1.Name AND T_Artworks.Person_ID IS NULL;

See it in action on SQL Fiddle: http://sqlfiddle.com/#!3/aa579/2

 

How does this work?

There are two queries combined into one – the UPDATE and the SELECT – both need to have a matching value – seen in the WHERE condition.

To recreate, start with the SELECT statement as it’s more likely to be more complicated. Build the SELECT statement so it has two values – the UPDATE value and the MATCHING value.

Then create the UPDATE statement, you can test it by removing the FROM etc portion.

Once you’re happy that both are working as expected, add the two statements together as shown above and run.