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 clause
it 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