none
Merge Statement Error Msg 8672, Level 16, State 1 RRS feed

  • Question

  • I get this error message (see below) even though my target row does not match more than 1 source row.  In my case, I believe that the problem is that my source and tatget rows differ for more than one attribute. It turns out that that is not uncommon in the data that I receive. Before I move away from the Merge statement altogether, does anyone know of a way to handle this problem? 

    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. 

    Just to make the issue clear, I need to be able to update a row in my target table when the date in the source table is different for more than one column.  Does anyone have any thoughts on this?


    Harold Jackson


    • Edited by Shannon Wade Friday, September 5, 2014 7:02 PM Sentence was wrong.
    Friday, September 5, 2014 7:01 PM

Answers

All replies

  • You need to define a better key to match the records (if possible), otherwise - as you mentioned - you have to go to a simple UPDATE statement
    Friday, September 5, 2014 7:04 PM
  • Show you to work with a loop to use merge:

    declare @cnt int=1
    
    WHILE @cnt>0
    
    Begin
    
    ;with mycte as (
    select id,  val FROM myTable  
    )
    
    ,mycte1 as (
    select id,  val  from mycte
    )
    
    ,mycte2 as (
    select id, val1, val val2 
    ,row_number()Over(Partition by id Order by id) rn 
    from mycte1 m 
    
    )
    
    merge myTable tgt
    using mycte2  src on tgt.id=src.id
    when matched and src.rn= 1 then 
    Update set Col=replace(col, src.val2, src.val1);
     
    set @cnt=@@ROWCOUNT
    
    end 

    Friday, September 5, 2014 7:14 PM
    Moderator
  • Thanks for the reply (don't see a name) but this won't work in my situation.  I was hoping that someone had a really novel way to get around the problem without a total recode but that looks like what I'll have to do. 

    Harold Jackson

    Friday, September 5, 2014 8:20 PM
  • How many date columns do you need to compare between the source and target tables?

    What is the exact rule for the join predicate?
    Friday, September 5, 2014 9:27 PM
  • I get this error message (see below) even though my target row does not match more than 1 source row.  In my case, I believe that the problem is that my source and tatget rows differ for more than one attribute. It turns out that that is not uncommon in the data that I receive. Before I move away from the Merge statement altogether, does anyone know of a way to handle this problem? 

    I don't see why it would matter if there is a difference in more than attribute. Then again, I don't see your code, so I have no idea what you are really talking about. But this error usually occurs when you join on incomplete keys.

    Post your code, and we may better understand your predicament.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, September 7, 2014 8:38 PM
  • Hi Shannon,

    What's the version of your SQL Server? If you are using SQL Server 2008, please install the latest Service Pack or CU for SQL Server to see if this issue still persists. Here is the KB article regarding this issue:
    You receive error message 8672 when you run a MERGE statement in SQL Server 2008: http://support.microsoft.com/kb/976316/en-us

    If not, please post more detail information as Erland suggested above. It will benefit for us to do further investigation and help you out of the trouble.

    If you have any feedback on our support, please click here.


    Elvis Long
    TechNet Community Support

    Wednesday, September 10, 2014 2:38 AM
    Moderator