Update same column, same DB with specific records

All we need is an easy explanation of the problem, so here it is.

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 "_Old" 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'

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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<>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<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0