none
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!

    Friday, January 04, 2013 8:14 PM

Answers

  • 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

    • Proposed as answer by sambeet Friday, January 04, 2013 9:26 PM
    • Marked as answer by Jon Griffin Friday, January 04, 2013 10:27 PM
    Friday, January 04, 2013 8:17 PM

All replies

  • 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

    • Proposed as answer by sambeet Friday, January 04, 2013 9:26 PM
    • Marked as answer by Jon Griffin Friday, January 04, 2013 10:27 PM
    Friday, January 04, 2013 8:17 PM
  • 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

    Friday, January 04, 2013 9:20 PM
  • This rocks. Thanks Naomi!

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

    Friday, January 04, 2013 10:27 PM