Updating a Table Using a Select

I recently had a situation at work where someone had exported data from our SQL Database into excel so they could update a column. Then they asked me to figure out how to update the data "my way", as they called it, so they didn't have to manually update all the changes. Coming from more of a programming background my first thought is to do things procedurally. I have done quite a bit of Perl and PHP coding so I would usually reach for a nice For Each loop and do it that way. Since T-SQL is set based I am having to retrain my brain to think in sets rather than procedures.

So I had a table like this:

pk_num	new_val
1	NULL
2	NULL
3	NULL

And I was given back a table like this:

pk_num	new_val
1	Frank
2	Joe
3	Sam

After scratching my head and looking through my big stack of books I decided the best way to do this would be a subquery. Lets call the original table table1, I imported the new table into SQL and called it table2. Here is the code I ended up using:

UPDATE table1

SET

new_val = table2.new_val

FROM

(SELECT pk_num, new_val

FROM table2) AS table2

WHERE

table1.pk_num = table2.pk_num

---------
EDIT: 8/16/2011
Since I am not using a where clause in the subquery, it is not actually needed. The query could be rewritten like it is below. I'm leaving it as an example and because it is the basis for the next part
UPDATE table1

SET

new_val = table2.new_val

FROM table2

WHERE

table1.pk_num = table2.pk_num

---------

It was a bit more complicated than that in that I didn't want to overwrite existing values in the new_val field in the original table if there was no value in the field in the update table so the end result actually looked like this:

UPDATE table1

SET

new_val = table2.new_val

FROM

(SELECT pk_num, new_val

FROM table2

WHERE new_val IS NOT null) AS table2

WHERE

table1.pk_num = table2.pk_num

Hope this helps someone. I know this is pretty basic stuff, but when you are just starting with T-SQL or, like me, are use to working with procedural or object oriented programming languages its's hard to train your brain to think in concepts like subqueries.

Hasta Luego,

RutherfordTX