How To Update Multiple Rows In A Temp Table With Multiple Values From Another Table Using Only One Id Common Between Them?

I am trying to reconcile the IDs in a temp table (top) from another DB table (bottom). Since I only have one ID that's common between the two, I am only getting the top result for

Solution 1:

Based on the comment - you have 2 values to match on, not just one? e.g., both GlobalRemunerationID and GlobalRemunerationGrantID?

Here's an example using tables 'temptable' and 't1'

UPDATE temptable
SET    ReconGlobalRemunerationGrantID = t1.GlobalRemunerationGrantID
FROM   temptable
       INNERJOIN t1 ON temptable.GlobalRemunerationID = t1.GlobalRemunerationID
                    AND temptable.GlobalRemunerationGrantID = t1.GlobalRemunerationGrantID

---

Update below

The below version takes the two data sets

• Partitions them by GlobalRemunerationID and orders them by ReconGlobalRemunerationGrantID to get the 'row numbers' (rn)
• Joins them on GlobalRemunerationID and rn to get them in order

Key code is below (with slightly different tables than your full set sorry - matches the data set you gave though).

; WITH tgrg AS
        (SELECT  GlobalRemunerationID, ReconGlobalRemunerationGrantID, 
                 ROW_NUMBER() OVER (PARTITION BY GlobalRemunerationID ORDERBY GlobalRemunerationGrantID) AS rn
            FROM #GlobalRemunerationGrant
        )
    UPDATE  tgrg 
    SET     ReconGlobalRemunerationGrantID = tgr.GlobalRemunerationGrantID 
    FROM    tgrg
            INNER JOIN 
               (SELECT   GlobalRemunerationID, GlobalRemunerationGrantID, 
                         ROW_NUMBER() OVER (PARTITION BY GlobalRemunerationID ORDERBY GlobalRemunerationGrantID) AS rn
                    FROM GlobalRemuneration
                ) AS tgr ON tgrg.GlobalRemunerationID = tgr.GlobalRemunerationID AND tgrg.rn = tgr.rn 

A db<>fiddle with the full set is there - note that I changed some of the IDs to demonstrate that it wasn;t using them to match.