SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Data setup

CREATE TABLE GlobalRemuneration (GlobalRemunerationGrantID int, GlobalRemunerationID int)
INSERT INTO GlobalRemuneration (GlobalRemunerationGrantID, GlobalRemunerationID) VALUES
(102895, 381332),
(102896, 381332),
(102905, 381332),
(102906, 381332)

CREATE TABLE #GlobalRemunerationGrant (GlobalRemunerationGrantID int, ReconGlobalRemunerationGrantID int, GlobalRemunerationID int)
INSERT INTO #GlobalRemunerationGrant (GlobalRemunerationGrantID, GlobalRemunerationID) VALUES
(100, 381332),
(101, 381332),
(102, 381332),
(103, 381332)

SELECT * FROM GlobalRemuneration
SELECT * FROM #GlobalRemunerationGrant


GlobalRemunerationGrantID
GlobalRemunerationID


102895
381332

102896
381332

102905
381332

102906
381332




GlobalRemunerationGrantID
ReconGlobalRemunerationGrantID
GlobalRemunerationID


100
null
381332

101
null
381332

102
null
381332

103
null
381332


-- Original version
UPDATE  tgrg 
SET     ReconGlobalRemunerationGrantID = tgr.GlobalRemunerationGrantID 
FROM    #GlobalRemunerationGrant tgrg
INNER JOIN GlobalRemuneration tgr on tgr.GlobalRemunerationID = tgrg.GlobalRemunerationID

SELECT * FROM #GlobalRemunerationGrant


GlobalRemunerationGrantID
ReconGlobalRemunerationGrantID
GlobalRemunerationID


100
102895
381332

101
102895
381332

102
102895
381332

103
102895
381332


-- Clear out recon field

UPDATE #GlobalRemunerationGrant SET ReconGlobalRemunerationGrantID = NULL
4 rows affected

-- Line-by-line version

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

               
SELECT * FROM #GlobalRemunerationGrant


GlobalRemunerationGrantID
ReconGlobalRemunerationGrantID
GlobalRemunerationID


100
102895
381332

101
102896
381332

102
102905
381332

103
102906
381332

GlobalRemunerationGrantID	ReconGlobalRemunerationGrantID	GlobalRemunerationID
100	null	381332
101	null	381332
102	null	381332
103	null	381332

GlobalRemunerationGrantID	ReconGlobalRemunerationGrantID	GlobalRemunerationID
100	102895	381332
101	102895	381332
102	102895	381332
103	102895	381332

GlobalRemunerationGrantID	ReconGlobalRemunerationGrantID	GlobalRemunerationID
100	102895	381332
101	102896	381332
102	102905	381332
103	102906	381332