Update matching keywords from first table to second table in SQL I have two tables where 1st table is Englishgermankwds_tbl and 2nd is Kwd_UploadRecored the data of the tables is as follows

Table Englishgermankwds_tbl

English_Keywords                 German_Keywords
architecture                     Architektur
Arrival                          Ankunft
aspirations                      Lebensziel
attire                           Kleidung
Blueprint                        Technische Zeichnung
Carrying                         Tragen
caucasian appearance             Europäischer Abstammung
cheerful                         fröhlich
clothes                          Kleidung
color image                      Farbbild
day                              Tag
Development                      Entwicklung
differential focus               Geringe Tiefenschärfe
focus on foreground              Fokus auf den Vordergrund
front view                       Vorderansicht
Full Length                      Ganzkörperansicht
growth                           Wachstum
Happiness                        Glücklichsein,Glück
Hardhat                          Bauarbeiterhelm


Table Kwd_UploadRecored

ID  Primary_Kwd                             Sec_Kwd                                         Main_Kwd
1   Man,One Man,architecture,Boy            Arrival,Sigle Man , Business Man ,Male          aspirations,One Person
2   Woman,attire,Girl                       Girl,Girls,Female,Blueprint,Carrying,           Teenage Girl,Only Girls
3   Grand father,Man,caucasian appearance   cheerful, Family,Fatherhood,Family Member,      Male Parent,
4   Baby ,clothes,color image               growth,Babies,Child,Happiness                   Children,Toddlers,differential focus,


I want to update the all English keywords of Kwd_UploadRecored  to German  which is matched from the English keywords of  Table Englishgermankwds_tbl

I want the expected result like-  Kwd_UploadRecored table would be  

ID  Primary_Kwd                                 Sec_Kwd                                         Main_Kwd
1   Man,One Man,Architektur,Boy                 Ankunft,Sigle Man , Business Man ,Male          Lebensziel,One Person
2   Woman,Kleidung,Girl                         Girl,Girls,Female,Technische Zeichnung,         Teenage Girl,Only Girls
3   Grand father,Man,Europäischer Abstammung    fröhlich, Family,Fatherhood,Family Member,      Male Parent,
4   Baby ,clothes,Farbbild                      Wachstum,Babies,Child,Glücklichsein             Children,Toddlers,differential focus,


Please help how to achieve this.
Refer the below solution for the Primary Keyword. 

create table Kwd_UploadRecored(ID int, Primary_Kwd varchar(200),Sec_Kwd varchar(200))
insert into Kwd_UploadRecored values
(1,'Man,One Man,architecture,Boy','Arrival,Sigle Man , Business Man ,Male'),
(2,'Woman,attire,Girl','Girl,Girls,Female,Blueprint,Carrying,'),
(3,'Grand father,Man,caucasian appearance','cheerful, Family,Fatherhood,Family Member,'),
(4,'Baby ,clothes,color image','growth,Babies,Child,Happiness')



create table Englishgermankwds_tbl([English Keywords] varchar(50), [German Keywords] varchar(50))
insert into Englishgermankwds_tbl values
('architecture','Architektur'),
('Arrival','Ankunft'),
('clothes','Kleidung')


Query

;WITH ctePrimary AS (
    SELECT ID,CAST('&lt;t&gt;' + REPLACE(Primary_Kwd, ',', '&lt;/t&gt;&lt;t&gt;') + '&lt;/t&gt;' AS XML) AS p
    FROM Kwd_UploadRecored
), ctePrimary1 AS (
    SELECT ID, x.value('.','VARCHAR(20)') AS Primary_Kwd
    FROM ctePrimary CROSS APPLY p.nodes('//t') a(x)
), ctePrimary2 AS ( --get German keyword if exist else English/Primary keyword
    SELECT A.ID,CASE 
    WHEN B.[German Keywords] IS NULL THEN A.Primary_Kwd ELSE B.[German Keywords] 
    END [German Keywords] 
    FROM ctePrimary1 A LEFT OUTER JOIN Englishgermankwds_tbl B ON A.Primary_Kwd=B.[English Keywords]
), ctePrimaryGroup AS (
    SELECT ID, 
       Stuff((SELECT ',' + [German Keywords] 
              FROM   ctePrimary2 
              WHERE  ID = a.ID 
              FOR xml path('')), 1, 1, '') [German Keywords]
    FROM   ctePrimary2 a 
    GROUP  BY  ID

)
Update Kwd_UploadRecored set Primary_Kwd = [German Keywords]
from Kwd_UploadRecored a inner join ctePrimaryGroup b on a.id=b.id