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

Question

我想从源表合并：

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

进入看起来像：

的目标表

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

我尝试过：

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;

这返回错误：

合并语句试图多次更新或删除同一行。当目标行与多个源行匹配时，就会发生这种情况。合并语句无法多次更新/删除目标表的同一行。完善ON子句，以确保目标行最多匹配一个源行，或者按子句使用组来分组源行。

任何帮助都将不胜感激。

Answer 1

错误消息导致您修改ON statement。您应该插入更多条件，以在表之间执行一对一的行匹配。所以;也许您也可以添加AddressId;

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

Answer 2

您需要创建两个过程，第一个是合并，它将添加新用户，第二个将更新具有更改的用户

更新任何用户

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