1

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'
0

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

1
  • 2
    Why do you need a subquery for s2 it appears to be unnecessary – Charlieface May 2 at 0:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.