February 27, 2016 at 5:28 am
Hi following is my stored procedure
create procedure [dbo].[Sp_AddPermission]
@id nvarchar(max)
as
declare @words varchar(max), @sql nvarchar(max)
set @words = @id
set @sql = 'merge admin AS target
using (values (''' + replace(replace(@words,';','),('''),'-',''',') + ')) AS source(uname, [add], [edit], [delete], [view],Block)
on target.uname = source.uname
when matched then update set [add] = source.[add], [edit] = source.[edit], [delete] = source.[delete], [view] = source.[view], [Block]=source.[Block];'
exec(@sql);
on execution following error is showing .
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.
How to resolve this
Regards
Baiju
February 27, 2016 at 6:10 am
That error means that a single row in the destination table has multiple matches in the source table. It's like telling you that John is 18 years old, Mary is 25 years old, and John is 38 years old, and then asking you to update the permanent records of John and Mary to reflect their current age.
The only way to resolve this is to either ensure that there are no duplicates in the source data, or to add a selection somewhere in the query to remove the duplicates from the set being used by the MERGE statement.
I will add that I have not attempted, and will not attempt, to figure out the weird and wonderful stuff you are doing in the dynamic SQL. Are you sure you need that? Are you aware of the vulnerability to SQL injection this exposes you to?
On first sight it looks as if you are trying to pass in a list of values. There are better ways to do that: http://www.sommarskog.se/arrays-in-sql.html.
February 27, 2016 at 8:02 am
baiju krishnan (2/27/2016)
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.
This error is one of the few redeeming qualities of choosing MERGE over the traditional BEGIN TRAN+UPDATE+INSERT+COMMIT TRAN approach, namely that it won't let you create ambiguity with your joins from the source to the target which the proprietary UPDATE syntax will allow.
As Hugo has alluded to, fix your join logic so you are not trying to update the same target row multiple times during your update.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 27, 2016 at 4:27 pm
baiju krishnan (2/27/2016)
The MERGE statement attempted to UPDATE or DELETE the same row more than once. [font="Arial Black"]This happens when a target row matches more than one source row.[/font] 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.How to resolve this
Fix the problem that has been identified in the error. You have duplicated data in the "uname" column of your source that needs to be reconciled (de-duplicated) before you attempt the merge or, as the others have stated, have insufficient criteria to uniquely identify rows in the source. Find the dupes and figure out what you want to do with them... keep the earliest, keep the latest, move both to an error table, add more criteria or ???
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply