使用第3个表更新表内连接

I have a Fact_Actuals yable with a new column "Segment_Id".

我有一个Fact_Actuals yable,新列“Segment_Id”。

I need to insert the segment id from DimsegmentMaster into Fact_Actuals.

我需要将DimsegmentMaster中的段ID插入Fact_Actuals。

Link is with SegmentMaster and Source Table column.

链接与SegmentMaster和Source Table列相关联。

Link is with FactTable is Measures of the Source Table column below.

链接与FactTable是下面的源表列的度量。

Kindly provide me the update query, as my below query is not fine.

请提供更新查询,因为我的下面的查询不是很好。

UPDATE Application_DB.[cdw].[Fact_Actuals] 
  set segment_sid =
  (SELECT c.SID
   FROM Application_DB.[cdw].[Fact_Actuals] b
   inner join Source_DB.STA.SourceTable a
     ON  convert(decimal(20,10),LTRIM(RTRIM(a.[K308]))) = b.NetExternalSales
     and convert(decimal(20,10),LTRIM(RTRIM(a.[K203]))) = b.Quantity_CON
     and convert(decimal(20,10),LTRIM(RTRIM(a.[K202]))) = b.Quantity_KG
   inner join Application_DB.cdw.DimSegmentMaster c
     ON RTRIM(a.[C005])=c.SegmentOriginal
  )

1 个解决方案

#1

Try this:

UPDATE b  
set segment_sid = c.sid
FROM Application_DB.[cdw].[Fact_Actuals] b
inner join Source_DB.STA.SourceTable a
ON 
    convert(decimal(20,10),LTRIM(RTRIM(a.[K308])))=b.NetExternalSales
    and convert(decimal(20,10),LTRIM(RTRIM(a.[K203])))=b.Quantity_CON
    and convert(decimal(20,10),LTRIM(RTRIM(a.[K202])))=b.Quantity_KG
inner join Application_DB.cdw.DimSegmentMaster c
ON RTRIM(a.[C005])=c.SegmentOriginal