The MERGE statement attempted to UPDATE or DELETE the same row more once

Question

I'd like to make a merge from a source table:

CreditAdvisorId SourceId    AddressID   AddressTypeId   CreditAdvisorAddressId  PersonTypeID    CountryId
14951   1   21311   1   36086   4   1
14951   1   21311   1   36086   4   1
14981   1   25773   1   NULL    4   1

into a target table which looks like:

CreditAdvisorAddressId  CreditAdvisorId AddressTypeId   PersonTypeID    AddressId   SourceId    CountryId   Created Modified    BeginDate   EndDate
0   6687    1   4   275479  1   1   2017-10-19 10:25:21.260 2017-10-19 10:25:21.260 2017-10-19 08:25:21.2605182 9999-12-31 23:59:59.9999999
1   5429    1   4   275479  1   1   2017-10-19 10:25:21.260 2017-10-19 10:25:21.260 2017-10-19 08:25:21.2605182 9999-12-31 23:59:59.9999999
2   2577    1   4   275490  1   1   2017-10-19 10:25:21.260 2017-10-19 10:25:21.260 2017-10-19 08:25:21.2605182 9999-12-31 23:59:59.9999999

I've tried:

MERGE [dim].[CreditAdvisorAddress] a
USING [stg].[CreditAdvisorAddress] b ON a.[CreditAdvisorAddressID] = b.[CreditAdvisorAddressID] 
                                     AND a.[SourceID] = b.[SourceID] 
                                     AND a.[CountryID] = b.[CountryID]

WHEN NOT MATCHED BY TARGET THEN
    INSERT ([CreditAdvisorId], [AddressTypeId], [PersonTypeID], [AddressId],
            [SourceId], [CountryId], [Created], [Modified])
    VALUES (b.[CreditAdvisorId], b.[AddressTypeId], b.[PersonTypeID], b.[AddressId],
            b.[SourceId], b.[CountryId], @now, @now)

WHEN MATCHED 
     AND a.[CreditAdvisorId] <> b.[CreditAdvisorId]
      OR a.[AddressTypeId] <> b.[AddressTypeId]
      OR a.[PersonTypeID] <> b.[PersonTypeID]
      OR a.[AddressId] <> b.[AddressId]
      --OR a.[SourceId] <> b.[SourceId]
      --OR a.[CountryId] <> b.[CountryId]
    THEN
        UPDATE 
           SET a.[CreditAdvisorId] = b.[CreditAdvisorId],
               a.[AddressTypeId] = b.[AddressTypeId],
               a.[PersonTypeID] = b.[PersonTypeID],
               a.[AddressId] = b.[AddressId],
               a.[SourceId] = b.[SourceId],
               a.[CountryId] = b.[CountryId],
               a.[Modified] = @now

OUTPUT $Action INTO @Summary;

This returns an 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.

Any help would be highly appreciated.

user_1370808 · Answer

The error message leads you to modify the ON statement. You should insert more condition to perform one-to-one row matching between tables. So; maybe you can add AddressId too;

USING [stg].[CreditAdvisorAddress] b ON a.[CreditAdvisorAddressID] = b.[CreditAdvisorAddressID] AND a.[SourceID] = b.[SourceID] AND a.[CountryID] = b.[CountryID] AND a.[AddressId] = b.[AddressId]

The MERGE statement attempted to UPDATE or DELETE the same row more >once

1 Answers