T-SQL Sub Query to update a Table by Joining with another map Table in SQL Server
USE [TAMATAM]
GO
UPDATE [TBL_Trans]
SET Week_Num= WK_Number
FROM
(
SELECT DISTINCT 'W'+CONVERT(VARCHAR,C.WK_Num) WK_Number,T.Week_Id FROM [TBL_Trans] T JOIN [TBL_Calendar] C
ON T.Week_Id=C.WK_Id AND C.[FY_QTR] =T.[QTR]
) S1
WHERE [TBL_Trans].Week_Id=S1.Week_Id
Example :
Lets suppose we have a Table called [TBL_Trans] as follows , where we want to update the 'Week_Num' by mapping with another table called [TBL_Calendar]
SELECT*FROM [TBL_Trans];
SELECT*FROM [TBL_Calendar];
Now we can update the [Week_Num] column of [TBL_Trans] by Joining with the map table [TBL_Calendar] :based on the key Column 'Week_Id' as follows ..........................
UPDATE [TBL_Trans]
SET Week_Num= WK_Number
FROM
(
SELECT DISTINCT 'W'+CONVERT(VARCHAR,C.WK_Num) WK_Number,T.Week_Id FROM [TBL_Trans] T JOIN [TBL_Calendar] C
ON T.Week_Id=C.WK_Id AND C.[FY_QTR] =T.[QTR]
) S1
WHERE [TBL_Trans].Week_Id=S1.Week_Id
Ouput :
USE [TAMATAM]
GO
UPDATE [TBL_Trans]
SET Week_Num= WK_Number
FROM
(
SELECT DISTINCT 'W'+CONVERT(VARCHAR,C.WK_Num) WK_Number,T.Week_Id FROM [TBL_Trans] T JOIN [TBL_Calendar] C
ON T.Week_Id=C.WK_Id AND C.[FY_QTR] =T.[QTR]
) S1
WHERE [TBL_Trans].Week_Id=S1.Week_Id
Example :
Lets suppose we have a Table called [TBL_Trans] as follows , where we want to update the 'Week_Num' by mapping with another table called [TBL_Calendar]
SELECT*FROM [TBL_Trans];
SELECT*FROM [TBL_Calendar];
Now we can update the [Week_Num] column of [TBL_Trans] by Joining with the map table [TBL_Calendar] :based on the key Column 'Week_Id' as follows ..........................
UPDATE [TBL_Trans]
SET Week_Num= WK_Number
FROM
(
SELECT DISTINCT 'W'+CONVERT(VARCHAR,C.WK_Num) WK_Number,T.Week_Id FROM [TBL_Trans] T JOIN [TBL_Calendar] C
ON T.Week_Id=C.WK_Id AND C.[FY_QTR] =T.[QTR]
) S1
WHERE [TBL_Trans].Week_Id=S1.Week_Id
Ouput :
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.