in MS SQL Want to update Distinct Values

Question

See this is my Tables; (my MS Sql Version is 2016)

Create table #UTIL_VwBranch (RegionID INT, BranchID INT)
INSERT INTO #UTIL_VwBranch VALUES(1001,1415)
INSERT INTO #UTIL_VwBranch VALUES(1001,1416)
INSERT INTO #UTIL_VwBranch VALUES(1001,1422)
INSERT INTO #UTIL_VwBranch VALUES(7701,1214)
INSERT INTO #UTIL_VwBranch VALUES(9791,5001)
INSERT INTO #UTIL_VwBranch VALUES(9791,5711)

Create table #Mast_Employees_Transfer (EmployeeCode VARCHAR(10),BranchID VARCHAR(MAX))
INSERT INTO #Mast_Employees_Transfer VALUES ('E09001','1415,1416,1422,1455,1458,1539,9911')
INSERT INTO #Mast_Employees_Transfer VALUES ('E09009','5001,5711')
INSERT INTO #Mast_Employees_Transfer VALUES ('S09009','1214')

Create table #ICT_Txn_UserRegistration ([EmpUserCode] VARCHAR(10), Permitted_Region_Codes VARCHAR(MAX), Permitted_Branch_Codes VARCHAR(MAX))
INSERT INTO  #ICT_Txn_UserRegistration	VALUES('E09001','','')	
INSERT INTO  #ICT_Txn_UserRegistration	VALUES('E09009','','')	
INSERT INTO  #ICT_Txn_UserRegistration	VALUES('S09009','','')	

-- I tried below Script 
UPDATE #ICT_Txn_UserRegistration
SET Permitted_Region_Codes = t1.RegionID,
    Permitted_Branch_Codes = t2.BranchID
FROM #ICT_Txn_UserRegistration AS u
JOIN #Mast_Employees_Transfer AS m ON u.EmpUserCode = m.EmployeeCode
JOIN #UTIL_VwBranch AS b ON CHARINDEX(CONVERT(VARCHAR, b.BranchID), m.BranchID) > 0
JOIN (SELECT DISTINCT m.EmployeeCode, STRING_AGG(b.RegionID, ',') AS RegionID
      FROM #Mast_Employees_Transfer AS m
      JOIN #UTIL_VwBranch AS b ON CHARINDEX(CONVERT(VARCHAR, b.BranchID), m.BranchID) > 0
      GROUP BY m.EmployeeCode) AS t1 ON u.EmpUserCode = t1.EmployeeCode
JOIN (SELECT DISTINCT m.EmployeeCode, STRING_AGG(b.BranchID, ',') AS BranchID
      FROM #Mast_Employees_Transfer AS m
      JOIN #UTIL_VwBranch AS b ON CHARINDEX(CONVERT(VARCHAR, b.BranchID), m.BranchID) > 0
      GROUP BY m.EmployeeCode) AS t2 ON u.EmpUserCode = t2.EmployeeCode;

but getting duplicated values, want to update distinct values in Permitted_Region_Codes. please provide right script.

Answer 1

Hello @Aypn CNN Add below update statement at the end of yours to do the final step

UPDATE #ICT_Txn_UserRegistration
SET Permitted_Region_Codes=
 STUFF(
(
    SELECT DISTINCT ',' + UniqNum FROM
    (
        SELECT CAST('<d>'+replace(Permitted_Region_Codes, ',','</d><d>')+'</d>' AS XML) AS numberXml
    ) as t1
    CROSS APPLY 
    (
     SELECT my_Data.D.value('.','varchar(50)') as UniqNum
     FROM t1.numberXml.nodes('d') as my_Data(D)
    ) t2
    FOR XML PATH('')
), 1, 1, '')
,Permitted_Branch_Codes=
 STUFF(
(
    SELECT DISTINCT ',' + UniqNum FROM
    (
        SELECT CAST('<d>'+replace(Permitted_Branch_Codes, ',','</d><d>')+'</d>' AS XML) AS numberXml
    ) as t1
    CROSS APPLY 
    (
     SELECT my_Data.D.value('.','varchar(50)') as UniqNum
     FROM t1.numberXml.nodes('d') as my_Data(D)
    ) t2
    FOR XML PATH('')
), 1, 1, '')

Regards Javier

Answer 2

It's not completely clear what you are struggling with here, as I think you have limited data. This query: SELECT DISTINCT m.EmployeeCode, STRING_AGG(b.RegionID, ',') AS RegionID FROM #Mast_Employees_Transfer AS m JOIN #UTIL_VwBranch AS b ON CHARINDEX(CONVERT(VARCHAR, b.BranchID), m.BranchID) > 0 GROUP BY m.EmployeeCode returns concatenated strings with string_agg, but if you remove that, and do this, you get just unique values. SELECT DISTINCT m.EmployeeCode, b.RegionID AS RegionID FROM #Mast_Employees_Transfer AS m JOIN #UTIL_VwBranch AS b ON CHARINDEX(CONVERT(VARCHAR, b.BranchID), m.BranchID) > 0 Is there a reason you want to aggregate these? Do you have a data set that includes multiple branches for an employee that are not duplicates? I suspect that if you have those, and you need to handle those, I would think about doing a cleaner join, getting the results, and then aggregating based on the employee id and disparate branches.

Answer 3

The issue here is that the list of region IDs isn't unique before you STRING_AGG -- look at the result of this query:

SELECT m.EmployeeCode, b.RegionID
FROM #Mast_Employees_Transfer AS m
JOIN #UTIL_VwBranch AS b ON CHARINDEX(CONVERT(VARCHAR, b.BranchID), m.BranchID) > 0

STRING_AGG won't dedupe this list for you when you aggregate over it -- you need to do that yourself, e.g.

SELECT EmployeeCode, STRING_AGG(RegionID, ',')
FROM (
	SELECT DISTINCT m.EmployeeCode, b.RegionID
	FROM #Mast_Employees_Transfer AS m
	JOIN #UTIL_VwBranch AS b ON CHARINDEX(CONVERT(VARCHAR, b.BranchID), m.BranchID) > 0
) t
GROUP BY EmployeeCode

(Using this directly might not be the best approach for your query, but you get the idea).

Answer 4

Your table DDL don't include primary keys; what are they?

Answer 5

Dear All, I tried and changed update script, it works for me, details as follows;

--Step:1 (
UPDATE #ICT_Txn_UserRegistration
SET Permitted_Region_Codes = t1.RegionID,
    Permitted_Branch_Codes = t2.BranchID
FROM #ICT_Txn_UserRegistration AS u
JOIN #Mast_Employees_Transfer AS m ON u.EmpUserCode = m.EmployeeCode
JOIN #UTIL_VwBranch AS b ON CHARINDEX(CONVERT(VARCHAR, b.BranchID), m.BranchID) > 0
JOIN (SELECT DISTINCT m.EmployeeCode,
            STUFF((SELECT ',' + CONVERT(VARCHAR, b.RegionID)
                   FROM #Mast_Employees_Transfer AS m2
                   JOIN #UTIL_VwBranch AS b ON CHARINDEX(CONVERT(VARCHAR, b.BranchID), m2.BranchID) > 0
                   WHERE m2.EmployeeCode = m.EmployeeCode
                   FOR XML PATH('')), 1, 1, '') AS RegionID
      FROM #Mast_Employees_Transfer AS m
      GROUP BY m.EmployeeCode) AS t1 ON u.EmpUserCode = t1.EmployeeCode
JOIN (SELECT DISTINCT m.EmployeeCode,
            STUFF((SELECT ',' + CONVERT(VARCHAR, b.BranchID)
                   FROM #Mast_Employees_Transfer AS m2
                   JOIN #UTIL_VwBranch AS b ON CHARINDEX(CONVERT(VARCHAR, b.BranchID), m2.BranchID) > 0
                   WHERE m2.EmployeeCode = m.EmployeeCode
                   FOR XML PATH('')), 1, 1, '') AS BranchID
      FROM #Mast_Employees_Transfer AS m
      GROUP BY m.EmployeeCode) AS t2 ON u.EmpUserCode = t2.EmployeeCode;

After execution completion of step1, then Im execute Step2 as below, then its trimmed duplicate Permitted_Region_Codes values

--Step:2
UPDATE #ICT_Txn_UserRegistration
SET Permitted_Region_Codes=
 STUFF(
(
    SELECT DISTINCT ',' + UniqNum FROM
    (
        SELECT CAST('<d>'+replace(Permitted_Region_Codes, ',','</d><d>')+'</d>' AS XML) AS numberXml
    ) as t1
    CROSS APPLY 
    (
     SELECT my_Data.D.value('.','varchar(50)') as UniqNum
     FROM t1.numberXml.nodes('d') as my_Data(D)
    ) t2
    FOR XML PATH('')
), 1, 1, '')
,Permitted_Branch_Codes=
 STUFF(
(
    SELECT DISTINCT ',' + UniqNum FROM
    (
        SELECT CAST('<d>'+replace(Permitted_Branch_Codes, ',','</d><d>')+'</d>' AS XML) AS numberXml
    ) as t1
    CROSS APPLY 
    (
     SELECT my_Data.D.value('.','varchar(50)') as UniqNum
     FROM t1.numberXml.nodes('d') as my_Data(D)
    ) t2
    FOR XML PATH('')
), 1, 1, '')

Thanks :)