1 Replies - 567 Views - Last Post: 06 June 2017 - 07:58 AM Rate Topic: -----

#1 Damage  Icon User is offline

  • Lord of Schwing
  • member icon

Reputation: 284
  • View blog
  • Posts: 1,961
  • Joined: 05-June 08

Merge statement with a user defined table

Posted 29 May 2017 - 12:00 PM

I have a stored proc that i pass a user defined table and it works when there is only one row but as soon as i put in more than one id into the table the merge statement puts out this error? almost like it tries to execute each row in the table more than one

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.



Here's some dummy data i'm using
IF OBJECT_ID( 'tempdb..#AttributeChanges') IS NOT NULL DROP TABLE #AttributeChanges;
DECLARE @TVP [UpdateTableType]
declare @ColumnName NVARCHAR(50)
declare @UpdateValue NVARCHAR(50)
declare @UserName NVARCHAR(50)
declare @AssetGroup NVARCHAR(50)
declare @AssetType NVARCHAR(50)

declare @ReasonForUpdate NVARCHAR(50)

insert into @TVP values ('TCC',1)
Set @ColumnName = 'AcquisitionType'
set @UpdateValue = 'SmokeyTheBearLovesYou'
set @UserName = 'TEST'
set @AssetGroup = 'TEST'
set @AssetType = 'TEST'

set @ReasonForUpdate = 'TEST'




insert into @TVP values ('TCC',1)
--insert into @TVP values ('TCC',2)
insert into @TVP values ('TCC',3)


exec [dbo].[sp_TCC_UpdateAssetAttributeFields] @TVP,@ColumnName,@UpdateValue,@UserName,@AssetGroup,@AssetType,@ReasonForUpdate





and heres the proc

ALTER PROCEDURE [dbo].[sp_TCC_UpdateAssetAttributeFields]  
    ( @TVP UpdateTableType READONLY
	 ,@ColumnName NVARCHAR(50)
	 ,@UpdateValue NVARCHAR(MAX)
	 ,@UserName NVARCHAR(20)
	 ,@AssetGroup NVARCHAR(30)
     ,@AssetType NVARCHAR(30)
	 ,@ReasonForUpdate NVARCHAR(MAX) )
	  
    AS   
   


IF OBJECT_ID( 'tempdb..#AttributeChanges') IS NOT NULL DROP TABLE #AttributeChanges;
CREATE TABLE #AttributeChanges (Id uniqueidentifier
           ,UserName nvarchar(50)
           ,AssetSequenceNumber bigint
           ,AttributeGroup nvarchar(30)
           ,AttributeType nvarchar(30)
           ,AttributeName nvarchar(56)
           ,AttributePreviousValue nvarchar(2000)
           ,AttributeUpdatedValue nvarchar(2000)
           ,ReasonForUpdate nvarchar(500)
           ,DateCreated datetime
           ,DateModified datetime)


MERGE  Gasset_Attribute AS Target
 USING @TVP AS Source
    ON Target.G1_ASSET_SEQ_NBR = Source.G1AssetSeqNum AND Target.SERV_PROV_CODE = Source.SERVPROVCODE
WHEN MATCHED 
		AND TARGET.G1_ATTRIBUTE_NAME = @ColumnName
THEN
	UPDATE SET
		G1_ATTRIBUTE_VALUE = @UpdateValue,REC_DATE = GETDATE(),REC_FUL_NAM = @UserName
WHEN NOT MATCHED BY TARGET
THEN
	INSERT
		([SERV_PROV_CODE],[G1_ASSET_SEQ_NBR],[G1_ATTRIBUTE_NAME],[G1_ATTRIBUTE_VALUE],[REC_DATE],[REC_FUL_NAM],[REC_STATUS],[RES_ID],[G1_IS_Hidden])
	VALUES(Source.SERVPROVCODE,Source.G1AssetSeqNum,@ColumnName,@UpdateValue,GETDATE(),@UserName,'A',NULL,NULL )
OUTPUT
NEWID(),
@UserName,
inserted.G1_ASSET_SEQ_NBR,
@AssetGroup,
@AssetType,
inserted.G1_ATTRIBUTE_NAME,
deleted.G1_ATTRIBUTE_Value,
inserted.G1_ATTRIBUTE_Value,
@ReasonForUpdate,
GETDATE(),
GETDATE()
into #AttributeChanges;




Is This A Good Question/Topic? 0
  • +

Replies To: Merge statement with a user defined table

#2 WabiSabi  Icon User is offline

  • D.I.C Head

Reputation: 52
  • View blog
  • Posts: 224
  • Joined: 31-December 10

Re: Merge statement with a user defined table

Posted 06 June 2017 - 07:58 AM

https://www.mssqltip...han-once-error/

I would write a simple select query to see what exactly you are getting when you join your source and target tables, see which rows it's trying to update more than once so you know how to limit the data.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1