How to split a multivalued column, do a lookup and update another column? This is a part of my previous question.

How can I achieve the following for all the records in my table using a single query?

SET @TeamMembers = TeamMembers from tblProjects where ID = '125'

DECLARE @MemberEmails TABLE(col VARCHAR(255))
DECLARE @MemberNames TABLE(col VARCHAR(255))
DECLARE @Names VARCHAR(255)

INSERT INTO @MemberEmails
SELECT * FROM [dbo].[fnSplitString] (@TeamMembers, ',')

INSERT INTO @MemberNames
SELECT u.NAME FROM @MemberEmails e LEFT OUTER JOIN Users u ON e.col = u.EMAILID

SELECT @Names =
   STUFF((SELECT '; ' + RTRIM(CONVERT(CHAR(50),col))
   FROM   @MemberNames b        
   FOR XML PATH('')),1,1,'') 

UPDATE tblProjects SET Team = @Names  WHERE ID = '125'
I was able to achieve the update of all records my table, using the below set of commands :

DECLARE @MemberNames TABLE(ID VARCHAR(50), Person VARCHAR(255))
INSERT INTO @MemberNames 
SELECT P.ID, u.NAME
FROM dbo.tblProjects AS P 
CROSS apply dbo.fnSplitString(P.TeamMembers, ',') AS S      
     LEFT OUTER JOIN Users u ON s.splitdata = u.EMAILID     

DECLARE @Names TABLE(ID VARCHAR(50), Name VARCHAR(MAX) ) 
INSERT INTO @Names
SELECT t.ID, STUFF(ISNULL((SELECT '; ' + x.Person
            FROM @MemberNames x
           WHERE x.ID = t.ID
        GROUP BY x.Person
         FOR XML PATH (''), TYPE).VALUE('.','VARCHAR(max)'), ''), 1, 2, '') 
    FROM @MemberNames t
    GROUP BY t.ID 
UPDATE tblProjects SET Team = Name FROM tblProjects pr INNER JOIN @Names ON ID = pr.ID


I have a table-valued function fnSplitString for splitting a string based on a delimiter.

p.s. I couldn't get the updates using a single query, as I had mentioned in my question.