CODEWITHSUNDEEP
sqlsql-servert-sqlcursor
Ahmad Mousavi
Ahmad Mousavi

Reputation: 601

How to update an attribute of a table with another table withouth using join or merge

I have a temp table called "customerInfo" as below:

|CustomerNumber |
|197210271883   |
|197210271883   |
|194905062891   |
|194905301380   |

And another table called "Person" as below:

|Id CustomerNumber  FirstName   LastName  |
|1  NULL            Jack        Axelsson  |
|2  198003147470    Sarah       Flöijer   |
|3  196305088491    Göte        Lagergren |

I need to Update CustomerNumber in the second table based on what I have in the first one. As you see, There is no relationship between them and I can not leverage join or merge techniques.

What I tried was using two cursors. I totally have no idea if its a right way or not?

DECLARE @value1 bigint
DECLARE db_cursor CURSOR FOR
SELECT CustomerNumber from customerInfo;

DECLARE @value2 bigint
DECLARE db_cursor_2 CURSOR FOR
SELECT CustomerNumber from Person;

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @value1   

OPEN db_cursor_2
FETCH NEXT FROM db_cursor_2 INTO @value2   


WHILE @@FETCH_STATUS = 0   
    BEGIN   
           update Person set  CustomerNumber = @value1
           where CustomerNumber = @value2

           FETCH NEXT FROM db_cursor INTO @value1 
           FETCH NEXT FROM db_cursor_2 INTO @value2 
    END   

CLOSE db_cursor
DEALLOCATE db_cursor
CLOSE db_cursor_2
DEALLOCATE db_cursor_2

Upvotes: 0

Views: 57

Answers (1)

MK_
MK_

Reputation: 1169

If I understood correctly, you want to trump over the existing values of CustomerNumber in your second table?

To do this, a cursor is not the best idea because they work sequentially. Maybe try something like this:

; WITH CTE1 AS (
    SELECT CustomerNumber, ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS SNO
    FROM customerInfo
), CTE2 AS (
    SELECT Id, CustomerNumber, ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS SNO
    FROM Person
)
UPDATE CTE2
SET CTE2.CustomerNumber = CTE1.CustomerNumber
FROM CTE1 
    INNER JOIN CTE2 ON CTE1.SNO = CTE2.SNO

This has not been tested in SQL Server so there may be some faults but you get the point from the code above.

Upvotes: 1

Related Questions