合并声明SSIS [英] Merge Statement SSIS

问题描述

我在SSMS上测试了此代码

 使用
合并dim_BTS AS Target（
 SELECTA。 BTS，D.idVille 
来自广播A 
内部联接dbo.DIM_AXE_GEO D 
 ON A.Ville = D.Villle 
 
）AS Source ON Source.BTS =匹配后的Target.BTS 
 
 
 
更新
 SET Target.idVille = Source.idVille; 
  

向我显示此错误

MERGE语句尝试多次更新或删除同一行

。当目标行与多个源
行匹配时，就会发生这种情况。 MERGE语句不能多次更新/删除
目标表的同一行。优化ON子句以确保目标
行最多匹配一个源行，或使用GROUP BY子句将
分组到源行。

能否请你帮我该怎么办？

解决方案

您的源子查询是返回具有相同 BTS （您用来连接目标的列）的重复行，这<< c $ c> MERGE 语句是不允许的。 / p>

您可以使用 ROW_NUMBER（）优化查询以仅过滤每个 BTS 的最新行。 ）在CTE中的功能

 与CTE_Source AS 
（
 SELECT A. BTS，D.idVille，ROW_NUMBER（）超过（按A.BTS排序或按d.idVille DESC排序）RN-选择您偏好的顺序
从播出A 
内部联接dbo.DIM_AXE_GEO D 
 ON A.Ville = D.Villle 
）
使用
合并dim_BTS AS目标（
 SELECT * FROM CTE_Source WRN RN = 1 
）AS Source ON Source防弹少年团=匹配后的Target.BTS 
然后
更新
 SET Target.idVille = Source.idVille; 
  

或者如果需要插入多行BTS，则在加入时需要在ON子句上添加更多列达到目标。

I tested this code on SSMS

Merge dim_BTS AS Target using  
(
    SELECT  A.BTS, D.idVille  
    FROM onAir A  
    INNER JOIN dbo.DIM_AXE_GEO D   
        ON A.Ville = D.Villle   

) AS Source ON Source.BTS = Target.BTS  


WHEN MATCHED THEN  
UPDATE  
SET Target.idVille = Source.idVille;  

show me this error

The MERGE statement attempted to UPDATE or DELETE the same row more
than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

Can you please help me what can I do ?

解决方案

Your Source sub-query is returning duplicate rows with same BTS (column You use to join on target) which is not allowed for MERGE statement.

You can refine your query to filter only the latest row for each BTS using ROW_NUMBER() function in CTE

WITH CTE_Source AS 
(
    SELECT  A.BTS, D.idVille, ROW_NUMBER() OVER (PARTITION BY A.BTS ORDER BY d.idVille DESC)  RN -- choose order of your preference
    FROM onAir A  
    INNER JOIN dbo.DIM_AXE_GEO D   
        ON A.Ville = D.Villle   
)
Merge dim_BTS AS Target using  
(
    SELECT  * FROM CTE_Source WHERE RN=1
) AS Source ON Source.BTS = Target.BTS  
WHEN MATCHED THEN  
UPDATE  
SET Target.idVille = Source.idVille; 

Or if multiple row BTS needs to be inserted, you need to add more columns on ON clause when joining on target.

这篇关于合并声明SSIS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！