troubleshooting Question

SQL MERGE - A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times

Avatar of SimonPrice3376
SimonPrice3376 asked on
Microsoft SQL ServerSQL
5 Comments1 Solution230 ViewsLast Modified:
I am working with a SQL Update script that is called from .Net a large number of times which is taking up CPU, Memory, LAN and Database resources and I want to put things into a User Defined Table Type and pass into a SQL Merge Statement so that its simpler and cleaner. Below is a replica of the data that I am being given to work with and what I am trying to do.

When I run this I get the error
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.
, and would be grateful if someone would be able to help me change my approach so that I can get this right and update everything as I need.

The field that I am updating on is part of a concatenated primary key. I am waiting on the actual interface file \ data to see what bits of informaiton I actually have to work with and am hoping that I can get the other key field and then life will be easy.

In the meantime, this is what I have to work with and need help on the off chance that the file I am being presented with doesnt have the other field.

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'testDb'
GO
USE [master]
GO
ALTER DATABASE [testDb] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object:  Database [testDb]    Script Date: 04/12/2017 10:09:14 ******/
DROP DATABASE [testDb]
GO


create database testDb 
go 

use testDb
go 

create table test
(
INT_CONT_CD			CHAR(4),
INT_CONT_CD_PURCH	CHAR(4),
SUPL_CD_TXT			VARCHAR(40),
SUPL_CD				CHAR(10)
)
go

CREATE TYPE test_TYP AS TABLE
(
INT_CONT_CD			CHAR(4),
INT_CONT_CD_PURCH	CHAR(4),
SUPL_CD_TXT			VARCHAR(40),
SUPL_CD				CHAR(10)
)

INSERT INTO TEST (INT_CONT_CD, INT_CONT_CD_PURCH, SUPL_CD_TXT, SUPL_CD) VALUES ('G','H','F','K');
INSERT INTO TEST (INT_CONT_CD, INT_CONT_CD_PURCH, SUPL_CD_TXT, SUPL_CD) VALUES ('G','F','F','K');
INSERT INTO TEST (INT_CONT_CD, INT_CONT_CD_PURCH, SUPL_CD_TXT, SUPL_CD) VALUES ('G','C','F','K');
INSERT INTO TEST (INT_CONT_CD, INT_CONT_CD_PURCH, SUPL_CD_TXT, SUPL_CD) VALUES ('G','E','F','K');
INSERT INTO TEST (INT_CONT_CD, INT_CONT_CD_PURCH, SUPL_CD_TXT, SUPL_CD) VALUES ('G','H','F','K');

SELECT * FROM test

DECLARE @TVP TABLE (
INT_CONT_CD			CHAR(4),
INT_CONT_CD_PURCH	CHAR(4),
SUPL_CD_TXT			VARCHAR(40),
SUPL_CD				CHAR(10));

INSERT INTO @TVP (INT_CONT_CD, INT_CONT_CD_PURCH, SUPL_CD_TXT, SUPL_CD) VALUES 
('PG','TH','IF','K'),
('PG','TH','IF','K'),
('PG','TH','IF','K'),
('PG','TH','IF','K'),
('PG','TH','IF','K'),
('PG','TH','IF','K')

MERGE TEST T
USING 
	@TVP S ON S.SUPL_CD = T.SUPL_CD
 
WHEN MATCHED THEN
	UPDATE SET
	T.INT_CONT_CD			= S.INT_CONT_CD			,
	T.INT_CONT_CD_PURCH		= S.INT_CONT_CD_PURCH	,
	T.SUPL_CD_TXT			= S.SUPL_CD_TXT			,
	T.SUPL_CD				= S.SUPL_CD	;
	
SELECT * FROM test		

Any and all help would be great
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros