Merge statement throwing Error !!

• vsamantha35

  SSChampion

  Points: 11551

  More actions

  May 31, 2017 at 2:01 am

  #338011

  Hi Experts,

  Need some help on MERGE statement. It is failing, with below error message. Eventhough there are no duplicate records either at Source/Destination table, still below error is thrown.

  Msg 8672, Level 16, State 1, Line 37
  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.

  demo data
  ==============

  create table #SrcTbl 
  (ID int,
  [Name] varchar(25) NULL,
  [Address] varchar(25) NULL
  )
  go

  create table #TrgTbl 
  (ID int,
  [Name] varchar(25) NULL,
  [Address] varchar(25) NULL
  )
  go
  truncate table #SrcTbl
  truncate table #TrgTbl

  --case 1: working fine and is expected behavior

  truncate table #SrcTbl
  truncate table #TrgTbl

  INSERT #SrcTbl values(1, 'A', '#201');
  INSERT #SrcTbl values(2, 'A', '#202');
  INSERT #SrcTbl values(3, 'A', '#203');
  go

  INSERT #TrgTbl values(1, 'A', NULL);
  go
  INSERT #TrgTbl values(999, 'ZZZ', '#394');
  go

  select * from #SrcTbl
  go
  select * from #TrgTbl
  go

  -- merge
  ;
  MERGE #TrgTbl AS TARGET USING
   (
  Select id, name, address
  from #SrcTbl
  ) AS Source(id,name,address)
  on TARGET.id=Source.id and TARGET.Name=Source.Name --join condition
  when not matched by target THEN
    INSERT values(Source.id,Source.Name, Source.Address)
  when matched then
    update set Name = Source.Name, Address = Source.Address
  when not matched by Source
    then delete;

  GO

  ---- (4 row(s) affected) ----

  Select * from #SrcTbl
  GO
  Select * from #TrgTbl
  GO

  --Case 2: which should fail and this is a expected behaviour.
  INSERT #SrcTbl values(1, 'A', '#202')
  INSERT #SrcTbl values(1, 'A', '#203')
  INSERT #SrcTbl values(1, 'A', '#203')
  INSERT #SrcTbl values(1, 'A', '#204')
  go

  INSERT #TrgTbl values(1, 'A', NULL);
  go

  select * from #SrcTbl
  go
  select * from #TrgTbl
  go

  -- merge
  ;
  MERGE #TrgTbl AS TARGET USING
   (
  Select id, name, address
  from #SrcTbl
  ) AS Source(id,name,address)
  on TARGET.id=Source.id and TARGET.Name=Source.Name --join condition
  when not matched by target THEN
    INSERT values(Source.id,Source.Name, Source.Address)
  when matched then
    update set Name = Source.Name, Address = Source.Address
  when not matched by Source
    then delete;

  GO

  Select * from #SrcTbl
  GO
  Select * from #TrgTbl
  GO

  /*
  Case 3: I am unable to repro the issue but on one of our prod servers Merge statement is failing with below error message.
  Is there anything I am missing here? anyother scenario in which we can repro below error with duplicate records ????
  */

  Msg 8672, Level 16, State 1, Line 37
  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.

  Thanks in advance.
  -Sam

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 31, 2017 at 2:40 am

  #1944682

  vsamantha35 - Wednesday, May 31, 2017 2:01 AM

  Eventhough there are no duplicate records either at Source/Destination table, still below error is thrown. 

  There are, though.  The key on which you are joining isn't unique in either table - 1, 'A' is repeated several times in both.  Is it possible to share the DDL (including constraints) for the real-life tables for which this is happening, please?

  John