Update same column, same DB with specific records
That wiuld only work when you have fixed rules
Like Doe and Doe_Old, so you can link the records.
IF you can't find a rule for the ON clauseit us impossible and you have to go over every record
Edit: it goes actually easier


CREATE tABLE  Staff (Role_Id  INT, Staff_First_Name VARCHAR(10), Staff_Last_Name VARCHAR(10));
INSERT INTO  Staff VALUES (10, 'Jane' , 'Doe_Old');

INSERT INTO  Staff VALUES (NULL, 'Jane', 'Doe')
GO




SELECT * FROM Staff;
GO


Role_Id | Staff_First_Name | Staff_Last_Name
------: | :--------------- | :--------------
     10 | Jane             | Doe_Old        
   null | Jane             | Doe            




Update s1 
Set Role_Id = s2.Role_Id
FROM Staff s1 
JOIN  Staff s2 ON s1.Staff_First_Name = s2.Staff_First_Name
                        AND s2.Staff_Last_Name = CONCAT(s1.Staff_Last_Name,'_Old')
GO



1 rows affected





SELECT * FROM Staff;
GO


Role_Id | Staff_First_Name | Staff_Last_Name
------: | :--------------- | :--------------
     10 | Jane             | Doe_Old        
     10 | Jane             | Doe            


db&lt;&gt;fiddle here
This Version is only necessary if you need some calculation made in th subquery


CREATE tABLE  Staff (Role_Id  INT, Staff_First_Name VARCHAR(10), Staff_Last_Name VARCHAR(10));
INSERT INTO  Staff VALUES (10, 'Jane' , 'Doe_Old');

INSERT INTO  Staff VALUES (NULL, 'Jane', 'Doe')
GO




SELECT * FROM Staff;
GO


Role_Id | Staff_First_Name | Staff_Last_Name
------: | :--------------- | :--------------
     10 | Jane             | Doe_Old        
   null | Jane             | Doe            




Update Staff
Set Role_Id = s2.Role_Id
FROM Staff s1 
JOIN
(SELECT Role_Id,Staff_First_Name,Staff_Last_Name
        FROM Staff) s2 ON s1.Staff_First_Name = s2.Staff_First_Name
                        AND s2.Staff_Last_Name = CONCAT(s1.Staff_Last_Name,'_Old')
GO




SELECT * FROM Staff;
GO


Role_Id | Staff_First_Name | Staff_Last_Name
------: | :--------------- | :--------------
     10 | Jane             | Doe_Old        
     10 | Jane             | Doe            


db&lt;&gt;fiddle here