"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.
"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:
inkaln
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.
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#.
Member
95 Points
428 Posts
MERGE Conflict when updating data through dataTable as parameter in Stored Procedure
Jul 29, 2019 06:17 PM|inkaln|LINK
"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....
Contributor
3140 Points
983 Posts
Re: MERGE Conflict when updating data through dataTable as parameter in Stored Procedure
Jul 30, 2019 05:09 AM|Yang Shen|LINK
Hi inkaln,
This error occurs when you have duplicates in your table.
It seems there are still duplicates after you set below:
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.
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:
In C# code:
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,
Or, please provide complete related code of how you create tbl and excute procedure in c#.
Best Regard,
Yang Shen