Update same column, same DB with specific records We are migrating a site from one Active Directory system to another but in the third-party app, we need to keep the old accounts, so we renamed the old accounts with &quot;_Old&quot; appended to the last name. Now we need to update a particular column from the old accounts into the new accounts so that their roles in the app match what they were before the migration.
I've figured out how to do it with one person.  I need to take this same logic and apply it to multiple accounts.
Here's the code I've got so far:
Update Staff
Set Role_Id =
    (   SELECT Role_Id 
        FROM Staff 
        where Staff_First_Name = 'Jane' 
            AND Staff_Last_Name = 'Doe_Old')
Where Staff_First_Name = 'Jane'
    AND Staff_Last_Name = 'Doe'
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