Merge Statement SSIS

Question

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 ?

Answer 1

Have a look at your SELECT statement. You may have to add DISTINCT, or another JOIN condition, or a WHERE clause to make sure rows are not duplicated.

Answer 2

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.