CODEWITHSUNDEEP
sqlsql-serversql-server-2016
user5021612
user5021612

Reputation:

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

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.

Upvotes: 2

Views: 9762

Answers (2)

Rachel
Rachel

Reputation: 119

You'll need to create two procedures, the first is the merge which will add new users and the second to update any users with changes

create procedure merge_table as
MERGE target_table_here T
USING source_table_here S
ON T.id = S.id
WHEN NOT MATCHED BY TARGET
    THEN
INSERT( 
    id,
    first_name,
    last_name)

values (
    S.id,
    S.first_name
    S.last_name)
-- For Updates ONLY IF THERE ARE NO DUPLICATES
 --WHEN MATCHED THEN UPDATE SET
      --T.id = S.id,
      --T.first_name = S.first_name,
      --T.last_name = S.last_name,
        

create procedure update_table as
UPDATE T
  SET
      T.id = S.id,
      T.first_name = S.first_name,
      T.last_name = S.last_name,
  FROM target_table_here AS T
  INNER JOIN source_table_here AS S
  ON 
      T.id = S.id or
      T.first_name = S.first_name or
      T.last_name = S.last_name

Upvotes: 0

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

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]

Upvotes: 2

Related Questions