How to update an inner join table with split_string?

I have one simple table, such as:

Table A:

Id	Role
1	Admin
2	Member
3	Reviewer
4	Contributor
5	Champion
6	Casual
7	Manager

I have 2 parameters, one is that @Id Nvarchar(max) = '1,2,3' that will contains values of all the Ids that I need to update. @Roles Nvarchar(max)='Member,Casual,Manager' that will be mapped to the @Ids above. So basically, I want to update Id 1 with value Member, Id 2 with Casual and Id 3 with Manager.

Result should be:

Id	Role
1	Member
2	Casual
3	Manager
4	Contributor
5	Champion
6	Casual
7	Manager

I was thinking about of split_string the both @Ids and @Roles, find the inner join table and update in a loop, but I don't know how it is going to implement in SQL server.

I would love to hear any feedback or a way on how to implement it in MS SQL server.

Thank you.

https://stackoverflow.com/questions/67238316/how-to-update-an-inner-join-table-with-split-string April 24, 2021 at 08:42AM