April 23, 2014 at 8:46 pm
I want to update table2 by checking table1.
if trauma has atleast 1 then clm2 in table2 would be 1
if infec has atleast 1 then clm2 in table2 would be 2
table1
Clm1 Clm2
Trauma 1
Trauma 1
Trauma 1
Infec 2
Infec 2
Trauma Null
table2
clm1 clm2
Trauma 1
infec 2
I have to update table2 based on table one ny checking multiple columns.
Please help me.
April 24, 2014 at 4:24 am
DBA12345 (4/23/2014)
I want to update table2 by checking table1.if trauma has atleast 1 then clm2 in table2 would be 1
if infec has atleast 1 then clm2 in table2 would be 2
I have to update table2 based on table one ny checking multiple columns.
As your description of the problem isn't very precise, I'm guessing here:
😎
DECLARE @TBL_01 TABLE
(
Clm1 VARCHAR(50) NOT NULL
,Clm2 INT NULL
);
DECLARE @TBL_02 TABLE
(
Clm1 VARCHAR(50) NOT NULL
,Clm2 INT NULL
);
INSERT INTO @TBL_01 (Clm1,Clm2)
VALUES
('Trauma' ,1 )
,('Trauma' ,1 )
,('Trauma' ,1 )
,('Infec' ,2 )
,('Infec' ,2 )
,('Trauma' ,Null );
INSERT INTO @TBL_02 (Clm1,Clm2)
VALUES
('Trauma' ,0 )
,('Infec' ,0 );
UPDATE T2
SET T2.Clm2 = Q1.Clm2
FROM
(
SELECT
X.Clm1
,CASE
WHEN X.Clm1 = 'Trauma' THEN 1
WHEN X.Clm1 = 'Infec' THEN 2
END AS Clm2
FROM
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY T1.Clm1,T1.Clm2
ORDER BY (SELECT NULL)
) AS CLM_RID
,T1.Clm1
,T1.Clm2
FROM @TBL_01 T1
WHERE T1.Clm2 IS NOT NULL
) AS X
WHERE X.CLM_RID = 1
) AS Q1 INNER JOIN @TBL_02 T2
ON Q1.Clm1 = T2.Clm1;
SELECT * FROM @TBL_02;
Results
Clm1 Clm2
------- -----
Trauma 1
Infec 2
April 24, 2014 at 8:44 am
This might be simpler.
WITH Clm2Values AS(
SELECT Clm1,
CASE Clm1 WHEN 'Trauma' THEN 1
WHEN 'Infec' THEN 2 END Clm2
FROM @TBL_01
GROUP BY Clm1
HAVING COUNT( Clm2) > 0
)
UPDATE t2 SET
Clm2 = v.Clm2
FROM @TBL_02 t2
JOIN Clm2Values v ON t2.Clm1 = v.Clm1
Unless we're missing something.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply