通过从多个表sql中选择值来更新表(Update table by selecting values from multiple tables sql)
我正在使用多个表中的值更新表。
UPDATE pv SET pv.[TotalDInTCG] = Dr.TCGDCnt ,pv.[AvgHbA1cImprovement] = hsd.AvgHbA1cCnt ,pv.[TotalCHDInTCG] = cpc.CHdCnt ,pv.[TotalCHDNTCG] = cpd1.CHdWithCholBPCnt ,pv.[PercentageOfCHDWithBPChol] = icp.CHFElligPopul ,pv.[TotalCOPDInTCG] = copd.TcgCOPDcount ,pv.[TotalCOPDMRCPOxySatuLevel] = copdmrc.TotalCOLevel ,pv.[PercentOferTCG] = copdOxSatu.oxySatligPopu FROM #tmpeTabel AS pv INNER JOIN #DRegistered AS Dr ON Dr.SK_ServiceProviderID = pv.SK_ServiceProviderID AND Dr.Years = pv.[ReportYear] AND Dr.Months = pv.[ReportMonth] INNER JOIN #HbA1cScoreDetails AS hsd ON hsd.SK_ServiceProviderID = pv.SK_ServiceProviderID AND hsd.Years = pv.[ReportYear] AND hsd.Months = pv.[ReportMonth] INNER JOIN #CHDCount as cpc ON cpc.SK_ServiceProviderID = pv.SK_ServiceProviderID AND cpc.Years = pv.[ReportYear] AND cpc.Months = pv.[ReportMonth] INNER JOIN #CHDBPCholPatientsDetails as cpd1 ON cpd1.SK_ServiceProviderID = pv.SK_ServiceProviderID AND cpd1.Years = pv.[ReportYear] AND cpd1.Months = pv.[ReportMonth] INNER JOIN #ImprovementCHFPerPractice as icp ON icp.SK_ServiceProviderID = pv.SK_ServiceProviderID AND icp.Years = pv.[ReportYear] AND icp.Months = pv.[ReportMonth] INNER JOIN #COPDCount as copd ON copd.SK_ServiceProviderID = pv.SK_ServiceProviderID AND copd.Years = pv.[ReportYear] AND copd.Months = pv.[ReportMonth] INNER JOIN #COPDDetailsMRCOxygenSatuLevel as copdmrc ON copdmrc.SK_ServiceProviderID = pv.SK_ServiceProviderID AND copdmrc.Years = pv.[ReportYear] AND copdmrc.Months = pv.[ReportMonth] INNER JOIN #ImprovPerElligPopula as copdOxSatu ON copdOxSatu.SK_ServiceProviderID = pv.SK_ServiceProviderID AND copdOxSatu.Years = pv.[ReportYear] AND copdOxSatu.Months = pv.[ReportMonth]这里的问题是，一些表有很多行，有些表的行数较少。 但是我在更新后为每个表获得的行数减少了
我应该为每个选择表使用可分离的更新..
I am updating the table with values from multiple tables.
UPDATE pv SET pv.[TotalDInTCG] = Dr.TCGDCnt ,pv.[AvgHbA1cImprovement] = hsd.AvgHbA1cCnt ,pv.[TotalCHDInTCG] = cpc.CHdCnt ,pv.[TotalCHDNTCG] = cpd1.CHdWithCholBPCnt ,pv.[PercentageOfCHDWithBPChol] = icp.CHFElligPopul ,pv.[TotalCOPDInTCG] = copd.TcgCOPDcount ,pv.[TotalCOPDMRCPOxySatuLevel] = copdmrc.TotalCOLevel ,pv.[PercentOferTCG] = copdOxSatu.oxySatligPopu FROM #tmpeTabel AS pv INNER JOIN #DRegistered AS Dr ON Dr.SK_ServiceProviderID = pv.SK_ServiceProviderID AND Dr.Years = pv.[ReportYear] AND Dr.Months = pv.[ReportMonth] INNER JOIN #HbA1cScoreDetails AS hsd ON hsd.SK_ServiceProviderID = pv.SK_ServiceProviderID AND hsd.Years = pv.[ReportYear] AND hsd.Months = pv.[ReportMonth] INNER JOIN #CHDCount as cpc ON cpc.SK_ServiceProviderID = pv.SK_ServiceProviderID AND cpc.Years = pv.[ReportYear] AND cpc.Months = pv.[ReportMonth] INNER JOIN #CHDBPCholPatientsDetails as cpd1 ON cpd1.SK_ServiceProviderID = pv.SK_ServiceProviderID AND cpd1.Years = pv.[ReportYear] AND cpd1.Months = pv.[ReportMonth] INNER JOIN #ImprovementCHFPerPractice as icp ON icp.SK_ServiceProviderID = pv.SK_ServiceProviderID AND icp.Years = pv.[ReportYear] AND icp.Months = pv.[ReportMonth] INNER JOIN #COPDCount as copd ON copd.SK_ServiceProviderID = pv.SK_ServiceProviderID AND copd.Years = pv.[ReportYear] AND copd.Months = pv.[ReportMonth] INNER JOIN #COPDDetailsMRCOxygenSatuLevel as copdmrc ON copdmrc.SK_ServiceProviderID = pv.SK_ServiceProviderID AND copdmrc.Years = pv.[ReportYear] AND copdmrc.Months = pv.[ReportMonth] INNER JOIN #ImprovPerElligPopula as copdOxSatu ON copdOxSatu.SK_ServiceProviderID = pv.SK_ServiceProviderID AND copdOxSatu.Years = pv.[ReportYear] AND copdOxSatu.Months = pv.[ReportMonth]The issue here is , some tables have many rows and some have less rows. But i am getting the less rows for all every table after update
Should i use update separably for each select table..
最满意答案
假设您在pv中更新的列都可以为空，这应该确保您捕获所有相关数据：
UPDATE pv SET pv.[TotalDInTCG] = Dr.TCGDCnt ,pv.[AvgHbA1cImprovement] = hsd.AvgHbA1cCnt ,pv.[TotalCHDInTCG] = cpc.CHdCnt ,pv.[TotalCHDNTCG] = cpd1.CHdWithCholBPCnt ,pv.[PercentageOfCHDWithBPChol] = icp.CHFElligPopul ,pv.[TotalCOPDInTCG] = copd.TcgCOPDcount ,pv.[TotalCOPDMRCPOxySatuLevel] = copdmrc.TotalCOLevel ,pv.[PercentOferTCG] = copdOxSatu.oxySatligPopu FROM #tmpeTabel AS pv LEFT JOIN #DRegistered AS Dr ON Dr.SK_ServiceProviderID = pv.SK_ServiceProviderID AND Dr.Years = pv.[ReportYear] AND Dr.Months = pv.[ReportMonth] LEFT JOIN #HbA1cScoreDetails AS hsd ON hsd.SK_ServiceProviderID = pv.SK_ServiceProviderID AND hsd.Years = pv.[ReportYear] AND hsd.Months = pv.[ReportMonth] LEFT JOIN #CHDCount as cpc ON cpc.SK_ServiceProviderID = pv.SK_ServiceProviderID AND cpc.Years = pv.[ReportYear] AND cpc.Months = pv.[ReportMonth] LEFT JOIN #CHDBPCholPatientsDetails as cpd1 ON cpd1.SK_ServiceProviderID = pv.SK_ServiceProviderID AND cpd1.Years = pv.[ReportYear] AND cpd1.Months = pv.[ReportMonth] LEFT JOIN #ImprovementCHFPerPractice as icp ON icp.SK_ServiceProviderID = pv.SK_ServiceProviderID AND icp.Years = pv.[ReportYear] AND icp.Months = pv.[ReportMonth] LEFT JOIN #COPDCount as copd ON copd.SK_ServiceProviderID = pv.SK_ServiceProviderID AND copd.Years = pv.[ReportYear] AND copd.Months = pv.[ReportMonth] LEFT JOIN #COPDDetailsMRCOxygenSatuLevel as copdmrc ON copdmrc.SK_ServiceProviderID = pv.SK_ServiceProviderID AND copdmrc.Years = pv.[ReportYear] AND copdmrc.Months = pv.[ReportMonth] LEFT JOIN #ImprovPerElligPopula as copdOxSatu ON copdOxSatu.SK_ServiceProviderID = pv.SK_ServiceProviderID AND copdOxSatu.Years = pv.[ReportYear] AND copdOxSatu.Months = pv.[ReportMonth]Assuming that the columns you are updating in pv are all nullable this should ensure you capture all the relevant data:
UPDATE pv SET pv.[TotalDInTCG] = Dr.TCGDCnt ,pv.[AvgHbA1cImprovement] = hsd.AvgHbA1cCnt ,pv.[TotalCHDInTCG] = cpc.CHdCnt ,pv.[TotalCHDNTCG] = cpd1.CHdWithCholBPCnt ,pv.[PercentageOfCHDWithBPChol] = icp.CHFElligPopul ,pv.[TotalCOPDInTCG] = copd.TcgCOPDcount ,pv.[TotalCOPDMRCPOxySatuLevel] = copdmrc.TotalCOLevel ,pv.[PercentOferTCG] = copdOxSatu.oxySatligPopu FROM #tmpeTabel AS pv LEFT JOIN #DRegistered AS Dr ON Dr.SK_ServiceProviderID = pv.SK_ServiceProviderID AND Dr.Years = pv.[ReportYear] AND Dr.Months = pv.[ReportMonth] LEFT JOIN #HbA1cScoreDetails AS hsd ON hsd.SK_ServiceProviderID = pv.SK_ServiceProviderID AND hsd.Years = pv.[ReportYear] AND hsd.Months = pv.[ReportMonth] LEFT JOIN #CHDCount as cpc ON cpc.SK_ServiceProviderID = pv.SK_ServiceProviderID AND cpc.Years = pv.[ReportYear] AND cpc.Months = pv.[ReportMonth] LEFT JOIN #CHDBPCholPatientsDetails as cpd1 ON cpd1.SK_ServiceProviderID = pv.SK_ServiceProviderID AND cpd1.Years = pv.[ReportYear] AND cpd1.Months = pv.[ReportMonth] LEFT JOIN #ImprovementCHFPerPractice as icp ON icp.SK_ServiceProviderID = pv.SK_ServiceProviderID AND icp.Years = pv.[ReportYear] AND icp.Months = pv.[ReportMonth] LEFT JOIN #COPDCount as copd ON copd.SK_ServiceProviderID = pv.SK_ServiceProviderID AND copd.Years = pv.[ReportYear] AND copd.Months = pv.[ReportMonth] LEFT JOIN #COPDDetailsMRCOxygenSatuLevel as copdmrc ON copdmrc.SK_ServiceProviderID = pv.SK_ServiceProviderID AND copdmrc.Years = pv.[ReportYear] AND copdmrc.Months = pv.[ReportMonth] LEFT JOIN #ImprovPerElligPopula as copdOxSatu ON copdOxSatu.SK_ServiceProviderID = pv.SK_ServiceProviderID AND copdOxSatu.Years = pv.[ReportYear] AND copdOxSatu.Months = pv.[ReportMonth]更多推荐
发布评论