locked
MERGE Conflict when updating data through dataTable as parameter in Stored Procedure RRS feed

  • Question

  • User-153404742 posted

    "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."

    I'm not sure why I'm getting the above error as I have used three columns in the ON clause to make sure records are distinct based on the three columns combined.  I removed any duplicated from the DataTable being passed but still getting the above error when trying to execute the storedProcedure.  I have the following table type created:


    CREATE TYPE [dbo].[ValueType] AS TABLE(
    [EntityId] [int] NOT NULL,
    [AttributeId] [int] NOT NULL,
    [ID] [varchar](256) NOT NULL,
    [Value] [varchar](500) NULL,
    [CreatedDTS] [datetime] NULL
    )

    I have the following stored procedure that takes dataTable as parameter

    ALTER PROCEDURE [dbo].[UpdateValues]
    @Dt ValueType READONLY
    AS
    BEGIN

    SET NOCOUNT ON;

    MERGE UserValue AS val
    USING (SELECT * FROM @Dt) AS Source
    ON val.EntityId= Source.EntityId AND val.AttributeId = Source.AttributeId AND val.ID = Source.ID
    WHEN MATCHED THEN
    UPDATE SET val.[Value] = Source.[Value]
    WHEN NOT MATCHED THEN
    INSERT (EntityId, AttributeId,ID, [Value])
    VALUES (Source.EntityId, Source.AttributeId, Source.ID, Source.[Value]);
    End

    In the code, I've placed the following to make sure rows are unique.

    cmd.CommandType = CommandType.StoredProcedure;

    DataView view = new DataView(tbl);
    DataTable distinctValues = view.ToTable(true, "EntityId", "AttributeId", "ID", "Value", "CreatedDTS");


    cmd.Parameters.Add(new SqlParameter("@Dt", SqlDbType.Structured));

    cmd.Parameters["@Dt"].Value = distinctValues;

    cmd.ExecuteNonQuery();

    How do I see which records are creating the MERGE conflict?  There are over 25,000 records being passed in the dataTable....

    Monday, July 29, 2019 6:17 PM

All replies

  • User-719153870 posted

    Hi inkaln,

    "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."

    This error occurs when you have duplicates in your table.

    It seems there are still duplicates after you set below:

    ON val.EntityId= Source.EntityId AND val.AttributeId = Source.AttributeId AND val.ID = Source.ID

    In your c# code, below part will not actually pick distinct dataview out of the datatable for you, because there are five columns in your dataview which you just want to three of them.

    DataView view = new DataView(tbl);
    DataTable distinctValues = view.ToTable(true, "EntityId", "AttributeId", "ID", "Value", "CreatedDTS");

    Please refer to: DataView.ToTable()

    Here i made a demo based on the code you provided and description.

    In SQL, create two tables and the procedure:

    CREATE table sourcetable(
    [EntityId] [int] NOT NULL,
    [AttributeId] [int] NOT NULL,
    [ID] [varchar](256) NOT NULL,
    [Value] [varchar](500) NULL,
    [CreatedDTS] [datetime] NULL
    )
    
    CREATE table targettable(
    [EntityId] [int] NOT NULL,
    [AttributeId] [int] NOT NULL,
    [ID] [varchar](256) NOT NULL,
    [Value] [varchar](500) NULL,
    [CreatedDTS] [datetime] NULL
    )
    
    insert into sourcetable values(1,10,'100','value1','2019-1-10')
    insert into sourcetable values(2,20,'200','value2','2019-2-10')
    insert into sourcetable values(3,30,'300','value3','2019-3-10')
    insert into sourcetable values(4,40,'400','value4','2019-4-10')
    insert into sourcetable values(5,50,'500','value5','2019-5-10')
    insert into sourcetable values(4,40,'400','value6','2019-6-10')
    insert into sourcetable values(5,50,'500','value7','2019-7-10')
    
    ---------------------------------------------
    CREATE TYPE [dbo].[ValueType] AS TABLE(
    [EntityId] [int] NOT NULL,
    [AttributeId] [int] NOT NULL,
    [ID] [varchar](256) NOT NULL,
    [Value] [varchar](500) NULL,
    [CreatedDTS] [datetime] NULL
    )
    ---------------------------------------------
    create proc [UpdateValues]
    @Dt targettable READONLY
    as
    begin
    SET NOCOUNT ON;
    MERGE sourcetable AS val
    USING (SELECT * FROM @Dt) AS Source
    ON val.EntityId= Source.EntityId AND val.AttributeId = Source.AttributeId AND val.ID = Source.ID
    WHEN MATCHED THEN 
    UPDATE SET val.[Value] = Source.[Value]
    WHEN NOT MATCHED THEN 
    INSERT (EntityId, AttributeId,ID, [Value])
    VALUES (Source.EntityId, Source.AttributeId, Source.ID, Source.[Value]);
    End

    In C# code:

    public string constr = ConfigurationManager.ConnectionStrings["DConString"].ConnectionString;
    
            protected DataTable GetSourceTable()
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    con.Open();
                    string sql = "select * from sourcetable";
                    SqlDataAdapter sda = new SqlDataAdapter(sql,con);
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    DataTable dt = ds.Tables[0];
                    return dt;
                }
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                SqlConnection con = new SqlConnection(constr);
    
                SqlCommand cmd = new SqlCommand("UpdateValues",con);
    
                con.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                DataTable tbl = GetSourceTable();
                DataView view = new DataView(tbl);
                DataTable distinctValues = view.ToTable(true, "EntityId", "AttributeId", "ID", "Value", "CreatedDTS");
    
                cmd.Parameters.Add(new SqlParameter("@Dt", SqlDbType.Structured));
    
                cmd.Parameters["@Dt"].Value = distinctValues;
    
                cmd.ExecuteNonQuery();
    
                con.Close();
            }

    This will reproduce yourproblem.

    My opion is that even you merge on three fields in your procedure, there are still some rows with same "EntityId", "AttributeId" and "ID".

    My suggestion is that you can try to filter when you create the tbl. For example,

    select * from sourcetable where value in (select min(value) from sourcetable group by EntityId)

    Or, please provide complete related code of how you create tbl and excute procedure in c#.

    Best Regard,

    Yang Shen

    Tuesday, July 30, 2019 5:09 AM