UPDATE table values from another table

--Shane Devane
--August 2005
--http://practicalsqlserver.blogspot.com

--BUSINESS TASK
--replace old phone number (values) with new 
--phone numbers (values) in members table

--CODE PURPOSE
--update a column value using the corrosponding 
--value in another table across all rows in the 
--table

--PROBLEM
--Two tables. table (A) is large (500K+ rows) and
--contains only 2 columns, 1 column is the old 
--value phone number (which is the value in table
--(b)) and the 2nd column in table (A) is the new
--value and needs to get put into table (b)

--SOLUTION
--get all the old values in table (b) and for 
--each value, loop through each value and simply
--UPDATE the with the new value

--STEPPING THROUGH
--1. using an INNER JOIN, get the old values 
--from the smaller table (b), and the get the new
--values from the large table (a) into a temp 
--table (inner join not used in example version!)
--2. loop through the results and UPDATE the old 
--value in table (b) with the new value.

--USAGE
--you will need to adjust a few lines of code 
--below
--(1) your main query to retrive the correct 
--"OLD Value" and new values
--(2) the UPDATE command you wish to perform on 
--each row(s)

USE Northwind

--initialise variables
DECLARE @oldValue VARCHAR(800)
DECLARE @replaceValue VARCHAR(800)

--get oldvalues and new values into temp table
IF      (
        object_id('tempdb..#TBCopy_08_2005') 
        IS NOT NULL
        )
        BEGIN
        DROP TABLE "#TBCopy_08_2005"
        END

CREATE TABLE #TBCopy_08_2005 (
oldValue VARCHAR(800),
replaceValue VARCHAR(800)
) 
DECLARE sqlCursor CURSOR LOCAL FOR 
        SELECT oldValue, replaceValue
        FROM #TBCopy_08_2005

--(1) populate temporary table
--[user needs to edit]
INSERT #TBCopy_08_2005
        (oldValue,replaceValue) 
        SELECT ContactName, ContactTitle
        FROM customers

--main loop 
OPEN sqlCursor
FETCH NEXT      FROM sqlCursor
                INTO @oldValue, @replaceValue
WHILE (@@FETCH_STATUS = 0)
        BEGIN

        --(2) perform update for rows
        --[user needs to edit]
        UPDATE orders  
        SET ShipName = @replaceValue
        WHERE ShipName = @oldValue
       
        FETCH NEXT      
        FROM sqlCursor
        INTO @oldValue, @replaceValue

        END
CLOSE sqlCursor

--clean up variables
DEALLOCATE sqlCursor