We help IT Professionals succeed at work.
Get Started

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

226 Views
Last Modified: 2017-12-04
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
Comment
Watch Question
Database Expert
Awarded 2016
Top Expert 2016
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE