SQL update 遇到的有关问题_Sql Server

SQL update 遇到的问题
需要更新comMaterialGroup表中的cu_tezhengchaxun字段  更新来源的字段为下面SQL问中的tezhengzhichaxun,
求UPDATE  SQL文怎么写.在线等 QQQ

查找汇总SQL文为
SELECT B.materialid,b.CU_realid,b.MaterialName,b.MaterialSpec,LEFT(StuList,LEN(StuList)) as tezhengzhichaxun
FROM (SELECT materialid,CU_realid,MaterialName,MaterialSpec,
     (SELECT shuxingzhi+';' FROM (select a.materialid,a.CU_realid,a.MaterialName,a.MaterialSpec,b.AttrCount as shuxingshuliang,c.rowid,d.AttributeName,
     d.AttributeName+':'+(CASE WHEN c.IsTempValue = 0 THEN e.AttrValueName ELSE c.TempAttrValue END) AS 'shuxingzhi'
      from comMaterialGroup a
      inner join comAttrStyleInfoData b on a.AttrStyleId = b.AttrStyleId
      inner join comAttributeInfoDetail c on a.AttrRefId = c.AttrRefId
      left join comAttribute d on c.attributeid = d.AttributeId
      left join comAttributeValues e on e.AttributeId = d.AttributeId and e.AttrValueId = c.AttrValueId
  where  (CASE WHEN c.IsTempValue = 0 THEN e.AttrValueName ELSE c.TempAttrValue END) is not NULL) as c
      WHERE c.materialid=A.materialid FOR XML PATH('')) AS StuList
      FROM (select a.materialid,a.CU_realid,a.MaterialName,a.MaterialSpec,b.AttrCount as shuxingshuliang,c.rowid,d.AttributeName,
           d.AttributeName +':'+(CASE WHEN c.IsTempValue = 0 THEN e.AttrValueName ELSE c.TempAttrValue END) AS 'shuxingzhi'
            from comMaterialGroup a
            inner join comAttrStyleInfoData b on a.AttrStyleId = b.AttrStyleId
            inner join comAttributeInfoDetail c on a.AttrRefId = c.AttrRefId
            left join comAttribute d on c.attributeid = d.AttributeId
            left join comAttributeValues e on e.AttributeId = d.AttributeId and e.AttrValueId = c.AttrValueId
where  (CASE WHEN c.IsTempValue = 0 THEN e.AttrValueName ELSE c.TempAttrValue END) is not NULL) as A
     GROUP BY a.materialid,a.CU_realid,a.MaterialName,a.MaterialSpec) as B
order by b.MaterialId
------解决方案--------------------

;with cte as
(SELECT B.materialid,b.CU_realid,b.MaterialName,b.MaterialSpec,LEFT(StuList,LEN(StuList)) as tezhengzhichaxun 
FROM (SELECT materialid,CU_realid,MaterialName,MaterialSpec,
     (SELECT shuxingzhi+';' FROM (select a.materialid,a.CU_realid,a.MaterialName,a.MaterialSpec,b.AttrCount as shuxingshuliang,c.rowid,d.AttributeName,
     d.AttributeName+':'+(CASE WHEN c.IsTempValue = 0 THEN e.AttrValueName ELSE c.TempAttrValue END) AS 'shuxingzhi'
      from comMaterialGroup a 
      inner join comAttrStyleInfoData b on a.AttrStyleId = b.AttrStyleId
      inner join comAttributeInfoDetail c on a.AttrRefId = c.AttrRefId
      left join comAttribute d on c.attributeid = d.AttributeId
      left join comAttributeValues e on e.AttributeId = d.AttributeId and e.AttrValueId = c.AttrValueId
  where  (CASE WHEN c.IsTempValue = 0 THEN e.AttrValueName ELSE c.TempAttrValue END) is not NULL) as c
      WHERE c.materialid=A.materialid FOR XML PATH('')) AS StuList
      FROM (select a.materialid,a.CU_realid,a.MaterialName,a.MaterialSpec,b.AttrCount as shuxingshuliang,c.rowid,d.AttributeName,
           d.AttributeName +':'+(CASE WHEN c.IsTempValue = 0 THEN e.AttrValueName ELSE c.TempAttrValue END) AS 'shuxingzhi'
            from comMaterialGroup a 
            inner join comAttrStyleInfoData b on a.AttrStyleId = b.AttrStyleId
            inner join comAttributeInfoDetail c on a.AttrRefId = c.AttrRefId