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

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		

Open in new window


Any and all help would be great
LVL 1
SimonPrice3376Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
This is because we have duplicate data in the Table variable. So it is updating same row again and again.

Please use this .

I have changed this line - ( SELECT DISTINCT * FROM @TVP ) S ON T.SUPL_CD = S.SUPL_CD

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 
	( SELECT DISTINCT * FROM @TVP ) S ON T.SUPL_CD = S.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	;

SELECT * FROM test	

Open in new window


OUTPUT

/*------------------------
OUTPUT	
------------------------*/

INT_CONT_CD INT_CONT_CD_PURCH SUPL_CD_TXT                              SUPL_CD
----------- ----------------- ---------------------------------------- ----------
PG          TH                IF                                       K         
PG          TH                IF                                       K         
PG          TH                IF                                       K         
PG          TH                IF                                       K         
PG          TH                IF                                       K         

(5 row(s) affected)

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Other option is to use JOIN like below. In this case you do not have to use DISTINCT ..JOIN WILL handle that...

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')

UPDATE T
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	
FROM TEST T
INNER JOIN @TVP S ON T.SUPL_CD = S.SUPL_CD 

SELECT * FROM test		

Open in new window


OUTPUT

/*------------------------
OUTPUT	
------------------------*/

INT_CONT_CD INT_CONT_CD_PURCH SUPL_CD_TXT                              SUPL_CD
----------- ----------------- ---------------------------------------- ----------
PG          TH                IF                                       K         
PG          TH                IF                                       K         
PG          TH                IF                                       K         
PG          TH                IF                                       K         
PG          TH                IF                                       K         

(5 row(s) affected)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
>>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.

If you have one more key to make data unique then your MERGE statement will work as it will mark the data unique for the statement.
0
SimonPrice3376Author Commented:
perfect thank you
0
Pawan KumarDatabase ExpertCommented:
welcome. glad to help as always. :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.