Update records in SQL by looking up in different table

I am copying data from few tables in SQL server A to B. I have a set of staging tables in B and need to update some of those staging tables based on updated values in final target table in B.

Example:

Server B:

StagingTable1:

ID | NAME | CITY
1    ABC    XYZ
2    BCD    XXX

StagingTable2:

ID | AGE | Table1ID(FK)
10    15    1
20    16    2

After Copying StagingTable1 to TargetTable1 (ID’s get auto polulated and I get new ID’s, now ID 1 becomes 2 and ID 2 becomes 3)

TargetTable1:

ID  | NAME | CITY
1     PQR    YYY  (pre-existing record)
2     ABC    XYZ
3     BCD    XXX

So now before I can copy the StagingTable2 I need to update the Table1ID column in it by correct values from TargetTable1.

StagingTable2 should become:

ID | AGE | Table1ID(FK)
10    15    2
20    16    3

I am writing a stored procedure for this and not sure how do I lookup and update the records in staging tables?

Answer

Assuming that (name, city) tuples are unique in StagingTable1 and TargetTable1, you can use an updatable common table expression to generate the new mapping and assign the corresponding values:

with cte as (
    select st2.Table1ID, tt1.id
    from StagingTable2 st2
    inner join StagingTable1 st1 on st1.ID = st2.Table1ID
    inner join TargetTable1 tt1 on tt1.name = st1.name and tt1.city = st1.city
)
update cte set Table1ID = id

Demo on DB Fiddle – content of StagingTable2 after the update:

id | age | Table1ID
-: | --: | -------:
10 |  15 |        2
20 |  16 |        3