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