Friday, January 04, 2013 8:14 PM
I have 2 tables, as follows:
I need to update NewDC.DCId = DC.DCId where NewDC.DCName_Temp = DC.DCName. So after the query runs, I'd have this recordset:
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.
SET NewDC.DCId = DC.DCId
WHERE (SELECT dc.DCName FROM DC INNER JOIN NewDC ON NewDC.DCName_Temp = dc.DCName)
Thanks for any help you can provide!
Friday, January 04, 2013 8:17 PMModerator
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
Friday, January 04, 2013 9:20 PMPlease 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), .. )
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
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
- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Friday, January 04, 2013 9:28 PM
Friday, January 04, 2013 10:27 PM
This rocks. Thanks Naomi!
I thought I was close. Thanks for closing the gap.