The MERGE statement attempted to UPDATE or DELETE the same row more than once

soumya · March 11, 2016, 12:48pm

Hi,

I have used Stored Procedure in sql server 2008 R2 sp3 for update/insert data from temp table to main table using merge statement. But Iam getting below error when no duplicate rows in the result.

Exception Type: System.Data.SqlClient.SqlException
Exception: 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.
Stack Trace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at rbk_Enrichment_Application.Code.Export_Data.Data_Export(String article_numbers)

JamesK · March 11, 2016, 1:43pm

The error message tells you what the problem is. Specifically, "This happens when a target row matches more than one source row.". Likely cause is that the join condition between Target and Source is not granular enough. There is more than one row in the Target table for a given combination of article, sizeindex and marketid.

Screenshots are usually much less useful than code posted as text because someone who wants to respond cannot copy it and make changes etc.

soumya · March 15, 2016, 5:28am

in my result set, I don't have any duplicate rows in source. for better understanding iam providing source any target rows.
source table result:-
article sizeIndex ean upc market_id literalSize
101 570 054871016745 cf 6.5
101 580 054871016752 cf 7
101 590 054871016769 cf 7.5
101 600 054871016776 cf 8
101 610 054871016783 cf 8.5
101 620 054871016790 cf 9
101 630 054871016806 cf 9.5
101 640 054871016813 cf 10
101 650 054871016820 cf 10.5
101 660 054871016837 cf 11
101 670 054871016844 cf 11.5
101 680 054871016851 cf 12
101 700 054871016868 cf 13
101 690 722977171452 cf 12.5
101 720 722977849504 cf 14
101 740 722977849511 cf 15
101 710 885589781607 cf 13.5
101 730 885589781614 cf 14.5
101 560 885589799855 cf 6
101 530 889131549196 cf 4.5
101 550 889131549202 cf 5.5
101 510 889131549219 cf 3.5
101 540 889131549226 cf 5
101 520 889131549233 cf 4

target table rows:-
article sizeindex ean upc market_id literalsize
101 510 889131549219 us 3.5
101 520 889131549233 us 4
101 530 889131549196 us 4.5
101 540 889131549226 us 5
101 550 889131549202 us 5.5
101 560 885589799855 us 6
101 570 054871016745 us 6.5
101 580 054871016752 us 7
101 590 054871016769 us 7.5
101 600 054871016776 us 8
101 610 054871016783 us 8.5
101 620 054871016790 us 9
101 630 054871016806 us 9.5
101 640 054871016813 us 10
101 650 054871016820 us 10.5
101 660 054871016837 us 11
101 670 054871016844 us 11.5
101 680 054871016851 us 12
101 690 722977171452 us 12.5
101 700 054871016868 us 13
101 710 885589781607 us 13.5
101 720 722977849504 us 14
101 730 885589781614 us 14.5
101 740 722977849511 us 15

soumya · March 15, 2016, 7:25am

After throwing the error also, data is updated into the target table in sqlserver 2008 R2 sp3. Please let me know what could be the cause for this error.

Kristen · March 15, 2016, 7:30am

You've got 6 column headings and 5 columns of data. Also no idea what the column datatypes might be, which could easily have a bearing on the outcome ...

Please provide the data as a CREATE #TEMP table and INSERT statements, otherwise each person here will have to create their own test and ... no surprise ... most people will just click onto the next thread rather than go to that trouble. Need Create Table for both eComSizeTemp and eComSize tables.

soumya · March 15, 2016, 9:22am

## Create table script for eComSizeTemp:-

USE [GEPS_RBK]
GO

/****** Object: Table [dbo].[eComSizeTemp] Script Date: 3/15/2016 2:47:23 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[eComSizeTemp](
[article] varchar NOT NULL,
[sizeIndex] varchar NOT NULL,
[ean] varchar NULL,
[upc] varchar NULL,
[market_id] nvarchar NOT NULL,
[literalSize] nvarchar NULL,
CONSTRAINT [eComSizeTemp$0] PRIMARY KEY CLUSTERED
(
[article] ASC,
[sizeIndex] ASC,
[market_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

**Create table script for eComSize:-

USE [GEPS_RBK]
GO

/****** Object: Table [dbo].[eComSize] Script Date: 3/15/2016 2:48:40 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[eComSize](
[article] varchar NOT NULL,
[sizeIndex] varchar NOT NULL,
[ean] varchar NULL,
[upc] varchar NULL,
[market_id] nvarchar NOT NULL,
[literalSize] nvarchar NULL,
CONSTRAINT [eComSize$0] PRIMARY KEY CLUSTERED
(
[article] ASC,
[sizeIndex] ASC,
[market_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Data for eComSizeTemp table:-

INSERT INTO eComSizeTemp VALUES('101','510',NULL,'889131549219','us','3.5')
INSERT INTO eComSizeTemp VALUES('101','520',NULL,'889131549233','us','4')
INSERT INTO eComSizeTemp VALUES('101','530',NULL,'889131549196','us','4.5')
INSERT INTO eComSizeTemp VALUES('101','540',NULL,'889131549226','us','5')
INSERT INTO eComSizeTemp VALUES('101','550',NULL,'889131549202','us','5.5')
INSERT INTO eComSizeTemp VALUES('101','560',NULL,'885589799855','us','6')
INSERT INTO eComSizeTemp VALUES('101','570',NULL,'054871016745','us','6.5')
INSERT INTO eComSizeTemp VALUES('101','580',NULL,'054871016752','us','7')
INSERT INTO eComSizeTemp VALUES('101','590',NULL,'054871016769','us','7.5')
INSERT INTO eComSizeTemp VALUES('101','600',NULL,'054871016776','us','8')
INSERT INTO eComSizeTemp VALUES('101','610',NULL,'054871016783','us','8.5')
INSERT INTO eComSizeTemp VALUES('101','620',NULL,'054871016790','us','9')
INSERT INTO eComSizeTemp VALUES('101','630',NULL,'054871016806','us','0.5')
INSERT INTO eComSizeTemp VALUES('101','640',NULL,'054871016813','us','10')
INSERT INTO eComSizeTemp VALUES('101','650',NULL,'054871016820','us','10.5')
INSERT INTO eComSizeTemp VALUES('101','660',NULL,'054871016837','us','11')
INSERT INTO eComSizeTemp VALUES('101','670',NULL,'054871016844','us','11.5')
INSERT INTO eComSizeTemp VALUES('101','680',NULL,'054871016851','us','12')
INSERT INTO eComSizeTemp VALUES('101','690',NULL,'722977171452','us','12.5')
INSERT INTO eComSizeTemp VALUES('101','700',NULL,'054871016868','us','13')
INSERT INTO eComSizeTemp VALUES('101','710',NULL,'885589781607','us','13.5')
INSERT INTO eComSizeTemp VALUES('101','720',NULL,'722977849504','us','14')
INSERT INTO eComSizeTemp VALUES('101','730',NULL,'885589781614','us','14.5')
INSERT INTO eComSizeTemp VALUES('101','740',NULL,'722977849511','us','15')

Data for eComSize table:-

INSERT INTO eComSize VALUES('101','510',NULL,'889131549219','us','3.5')
INSERT INTO eComSize VALUES('101','520',NULL,'889131549233','us','4')
INSERT INTO eComSize VALUES('101','530',NULL,'889131549196','us','4.5')
INSERT INTO eComSize VALUES('101','540',NULL,'889131549226','us','5')
INSERT INTO eComSize VALUES('101','550',NULL,'889131549202','us','5.5')
INSERT INTO eComSize VALUES('101','560',NULL,'885589799855','us','6')
INSERT INTO eComSize VALUES('101','570',NULL,'054871016745','us','6.5')
INSERT INTO eComSize VALUES('101','580',NULL,'054871016752','us','7')
INSERT INTO eComSize VALUES('101','590',NULL,'054871016769','us','7.5')
INSERT INTO eComSize VALUES('101','600',NULL,'054871016776','us','8')
INSERT INTO eComSize VALUES('101','610',NULL,'054871016783','us','8.5')
INSERT INTO eComSize VALUES('101','620',NULL,'054871016790','us','9')
INSERT INTO eComSize VALUES('101','630',NULL,'054871016806','us','0.5')
INSERT INTO eComSize VALUES('101','640',NULL,'054871016813','us','10')
INSERT INTO eComSize VALUES('101','650',NULL,'054871016820','us','10.5')
INSERT INTO eComSize VALUES('101','660',NULL,'054871016837','us','11')
INSERT INTO eComSize VALUES('101','670',NULL,'054871016844','us','11.5')
INSERT INTO eComSize VALUES('101','680',NULL,'054871016851','us','12')
INSERT INTO eComSize VALUES('101','690',NULL,'722977171452','us','12.5')
INSERT INTO eComSize VALUES('101','700',NULL,'054871016868','us','13')
INSERT INTO eComSize VALUES('101','710',NULL,'885589781607','us','13.5')
INSERT INTO eComSize VALUES('101','720',NULL,'722977849504','us','14')
INSERT INTO eComSize VALUES('101','730',NULL,'885589781614','us','14.5')
INSERT INTO eComSize VALUES('101','740',NULL,'722977849511','us','15')

Kristen · March 15, 2016, 10:17am

Does this return anything?

SELECT TOP 100
	S.article, S.sizeIndex, S.market_id, [Count]=COUNT(*)
FROM	eComSize AS T
	JOIN eComSizeTemp AS S
		 ON S.article = T.article
		AND LTrim(RTrim(S.sizeIndex)) = T.sizeIndex
		AND LTrim(RTrim(S.market_id)) = T.market_id
GROUP BY S.article, S.sizeIndex, S.market_id
HAVING COUNT(*) > 1
ORDER BY [Count] DESC, S.article, S.sizeIndex, S.market_id

soumya · March 15, 2016, 10:20am

I have executed your query. but nothing in the result.

Kristen · March 15, 2016, 10:23am

Sounds like good news but I wonder if there could be a space lurking in eComSize (that said, I would have expected that to show up in the earlier query)

SELECT	[article] = SUM(CASE WHEN T.article LIKE ' %' OR T.article LIKE '% ' 
		THEN 1 ELSE 0 END)
	, [sizeIndex] = SUM(CASE WHEN T.sizeIndex LIKE ' %' OR T.sizeIndex LIKE '% ' 
		THEN 1 ELSE 0 END)
	, [market_id] = SUM(CASE WHEN T.market_id LIKE ' %' OR T.market_id LIKE '% ' 
		THEN 1 ELSE 0 END)
FROM	eComSize AS T

Hopefully they are all zeros. If so I'm stumped, sorry.

Kristen · March 15, 2016, 10:26am

Might help others if you could post the MERGE statement (in the image above) as text. if you use

    ```sql

    .... your code here ...

    ```

then it will be formatted rather than word-wrapping all over the place!

soumya · March 15, 2016, 10:35am

anything wrong in merge statement or tables. Could you please explain clearly, if you don't mind?

Kristen · March 15, 2016, 10:57am

I can't see anything obvious, but because it is in an image I can't test it or try anything, so the only feedback you are getting is what I (fail to) see at a quick glance of your image.

hence posting the source code is preferable as people here can then try various things

soumya · March 15, 2016, 11:00am

## Here is the merge statement query

DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));

MERGE dbo.eComSize AS Target
USING (
SELECT * FROM eComSizeTemp
) AS Source
ON (Target.article = Source.article AND
Target.sizeIndex = LTRIM(RTRIM(Source.sizeIndex)) AND
Target.market_id = LTRIM(RTRIM(Source.market_id)))
WHEN MATCHED THEN
UPDATE
SET
Target.[ean] = LTRIM(RTRIM(ISNULL(Source.[ean], Target.[ean]))),
Target.[upc] = LTRIM(RTRIM(ISNULL(Source.[upc], Target.[upc]))),
Target.[literalSize] = LTRIM(RTRIM(Source.[literalSize]))

	   WHEN NOT MATCHED BY TARGET THEN
		INSERT ([article]
				,[sizeIndex]
				,[ean]
				,[upc]
                ,[market_id],[literalSize])	
		VALUES(LTRIM(RTRIM(Source.[article])),
				LTRIM(RTRIM(Source.[sizeIndex])),
				LTRIM(RTRIM(Source.[ean])),
				LTRIM(RTRIM(Source.[upc])),
                LTRIM(RTRIM(Source.[market_id])),
                LTRIM(RTRIM(Source.[literalSize])))OUTPUT $action INTO @SummaryOfChanges;

James · March 16, 2016, 8:18pm

I took the DDL and DML and ran it locally. I then used the MERGE statement and had no problems. Is it possible the data you posted is not representative of what is causing the error? Your sample tables have the correct PK's defined and the MERGE uses those for the matching. You do the trimming with the ON section but that should not be an issue. I also ran the SQL Kristen posted and got no rows meaning no duplicated in the source data posted.

The error is telling you exactly what is wrong and that tells me that it's a data issue somewhere.

scarela · March 17, 2016, 1:46am

I ran your code in my system and everything worked fine. Something is wrong with your data, but you didn't post it in the data sample.