Well... As
Mika Wendelius[
^] said, you have to re-think your database structure.
In the mean-while, have a look at this solution:
DECLARE @tab1 TABLE(EmpID INT, casualLeaves INT, SickLeaves INT)
INSERT INTO @tab1 (EmpID, casualLeaves , SickLeaves)
VALUES(1, 3, 2)
DECLARE @tab2 TABLE(EmpID INT, LeavesBalance VARCHAR(50))
INSERT INTO @tab2 (EmpID, LeavesBalance)
VALUES(1, '1 CL,1 SL')
;WITH BalanceToLeaves AS
(
SELECT EmpID, LEFT(LeavesBalance, CHARINDEX(',', LeavesBalance)-1) AS SingleLeave, RIGHT(LeavesBalance, LEN(LeavesBalance) -CHARINDEX(',', LeavesBalance)) AS Remainder
FROM @tab2
WHERE CHARINDEX(',', LeavesBalance)>0
UNION ALL
SELECT EmpID, LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS SingleLeave, RIGHT(Remainder, LEN(Remainder) -CHARINDEX(',', Remainder)) AS Remainder
FROM BalanceToLeaves
WHERE CHARINDEX(',', Remainder)>0
UNION ALL
SELECT EmpID, Remainder AS SingleLeave, NULL AS Remainder
FROM BalanceToLeaves
WHERE CHARINDEX(',', Remainder)=0
)
UPDATE C SET C.casualLeaves = C.casualLeaves + B.CasualLeave,
C.SickLeaves = C.SickLeaves + B.SickLeave
FROM (
SELECT EmpID, SUM(CasualLeave) AS CasualLeave, SUM(SickLeave) AS SickLeave
FROM (
SELECT EmpID, SingleLeave, CASE WHEN SingleLeave LIKE '%CL' THEN CONVERT(INT, LEFT(SingleLeave, CHARINDEX(' ', SingleLeave)-1)) END AS CasualLeave,
CASE WHEN SingleLeave LIKE '%SL' THEN CONVERT(INT, LEFT(SingleLeave, CHARINDEX(' ', SingleLeave)-1)) END AS SickLeave
FROM BalanceToLeaves) AS A
GROUP BY EmpID ) AS B INNER JOIN @tab1 AS C ON B.EmpID = C.EmpID
After update
@tabl
contains:
EmpID casualLeaves SickLeaves
1 4 3
For further details, please see:
WITH common_table_expression (Transact-SQL)[
^]
Using Common Table Expressions[
^]
Recursive Queries Using Common Table Expressions[
^]
UPDATE from SELECT using SQL Server - Stack Overflow[
^]