Update query to modify any number of specific rows - how to specify row and loop?

Question

I have 2 tables, as follows:

DC
DCId, DCName
111, California
222, Nevada
444, Colorado
777, Idaho

NewDC
DCId, DCName_Temp
NULL, California
NULL, Utah
NULL, Nevada
888, Montana

I need to update NewDC.DCId = DC.DCId where NewDC.DCName_Temp = DC.DCName. So after the query runs, I'd have this recordset:

NewDC
DCId, DCName_Temp
111, California
NULL, Utah
222, Nevada
888, Montana

I have up to 100 records to update at a time.

Here's my code, but I can't figure out how to a) restrict the WHERE condition to only one row to update and b) How to loop through all the rows that need updating.

UPDATE NewDC
 SET NewDC.DCId = DC.DCId
FROM DC
WHERE (SELECT dc.DCName FROM DC INNER JOIN NewDC ON NewDC.DCName_Temp = dc.DCName)

Thanks for any help you can provide!

Answer 1

Try

UPDATE NewDC
 SET DCId = DC.DCId 
FROM DC INNER JOIN NewDC
ON NewDC.DCName_Temp = dc.DCName

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Please learn to post DDL and not narratives. This is basic Netiquette. It is posted at the start of this forum. 

You also need to learn the basic terms and concepts; rows are not records. Why do you have a staging table instead of using a long parameter list? The important thing, however, not to ever use the old 1970's Sybase UPDATE..FROM.. syntax. It does not work! Google it! 

Learn SQL and how the MERGE statement works.  This is how we are writing code today. Here is a skeleton: 

CREATE PROCEDURE Update_DC
((@d1, @n1),(@d2, @n2), .. )
AS 
MERGE INTO DC  -- what is a “dc”? 
USING (VALUES (@d1, @n1),(@d2, @n2), .. ) AS Source (dc_id, dc_name) 
ON DC.dc_name = Source.dc_name
WHEN MATCHED
THEN UPDATE
 SET dc_id = Source.dc_id; 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 3

This rocks. Thanks Naomi!

I thought I was close. Thanks for closing the gap.