SQLTeam.com | Weblogs | Forums

Error showing in merge statement in Tsql

sql2008r2

#1

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


#2

I just copied your code and added print command (always a good idea with dynamic sql):

merge admin AS target
using (values ('abc)) 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];

you can see that the quotes are unbalanced, which is probably your problem