How to Update a Table by Joining with another Map Table using Sub Query in SQL Server

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 :

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence Professional

--------------------------------------------------------------------------------------------------------