sql - Merge Statement SSIS -

i tested code on ssms

merge dim_bts target using   (     select  a.bts, d.idville       onair       inner join dbo.dim_axe_geo d            on a.ville = d.villle     ) source on source.bts = target.bts     when matched   update   set target.idville = source.idville;   

show me error

the merge statement attempted update or delete same row more
once. happens when target row matches more 1 source row. merge statement cannot update/delete same row of target table multiple times. refine on clause ensure target row matches @ 1 source row, or use group clause group source rows.

can please me can ?

your source sub-query returning duplicate rows same bts (column use join on target) not allowed merge statement.

you can refine query filter latest row each bts using row_number() function in cte

with cte_source  (     select  a.bts, d.idville, row_number() on (partition a.bts order d.idville desc)  rn -- choose order of preference     onair       inner join dbo.dim_axe_geo d            on a.ville = d.villle    ) merge dim_bts target using   (     select  * cte_source rn=1 ) source on source.bts = target.bts   when matched   update   set target.idville = source.idville;  

or if multiple row bts needs inserted, need add more columns on on clause when joining on target.