locked
Error inmerge statement in Tsql RRS feed

  • Question

  • User1839056048 posted

    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

    </div>

    Saturday, February 27, 2016 11:33 AM

Answers

  • User-219423983 posted

    Hi klbaiju,

    I have created a demo as below code and you could have a look. For your code, you’d better first debug the “@sql” to check whether the “@sql” has the excepted values and then run the values to check whether it’s right or not. In my below code, I have made some changes on building the “@sql” and you could have a look. If the format of the value “@id” is not right, you could try to modify my code according to your need, or you could change it to meet with below format.

    create table dbo.[admin] 
    (
    	[uname] varchar(20),
    	[add] varchar(20),
    	[edit] varchar(20),
    	[delete] varchar(20),
    	[view] varchar(20),
    	[Block] varchar(20)
    )
    insert into dbo.[admin] values ('NAME','AA','BB','CC','DD','EE')
    insert into dbo.[admin] values ('NAME1','AA1','BB1','CC1','DD1','EE1')
    
    select * from dbo.[admin]
    
    declare @id nvarchar(max)='NAME;a-b-c-d-e'
    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);
    
    select * from dbo.[admin]
    
    drop table dbo.[admin]
    

    When I debug the above code, the “@sql” get the below result.

    merge admin AS target
    using (values ('NAME','a','b','c','d','e')) 
    	  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];

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 1, 2016 6:08 AM

All replies

  • User753101303 posted

    Hi,

    Seems you have multiple time the same word in @words. Have you checked that?

    Saturday, February 27, 2016 3:23 PM
  • User-219423983 posted

    Hi klbaiju,

    I have created a demo as below code and you could have a look. For your code, you’d better first debug the “@sql” to check whether the “@sql” has the excepted values and then run the values to check whether it’s right or not. In my below code, I have made some changes on building the “@sql” and you could have a look. If the format of the value “@id” is not right, you could try to modify my code according to your need, or you could change it to meet with below format.

    create table dbo.[admin] 
    (
    	[uname] varchar(20),
    	[add] varchar(20),
    	[edit] varchar(20),
    	[delete] varchar(20),
    	[view] varchar(20),
    	[Block] varchar(20)
    )
    insert into dbo.[admin] values ('NAME','AA','BB','CC','DD','EE')
    insert into dbo.[admin] values ('NAME1','AA1','BB1','CC1','DD1','EE1')
    
    select * from dbo.[admin]
    
    declare @id nvarchar(max)='NAME;a-b-c-d-e'
    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);
    
    select * from dbo.[admin]
    
    drop table dbo.[admin]
    

    When I debug the above code, the “@sql” get the below result.

    merge admin AS target
    using (values ('NAME','a','b','c','d','e')) 
    	  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];

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 1, 2016 6:08 AM