InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 11:12:23 GMT20http://www.sqlservercentral.com/Forums/Topic1384193-392-1.aspxThe error in the MERGE statement is encountered when one row in the target table will be addressed by more than one row in the source data set.This imples either that the matching criterium is incorrect, or there is more than one update per record in the source dataset.Wed, 14 Nov 2012 01:52:51 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1384193-392-1.aspxI have a table sectioninstructorSectionid,Instructorid,Instructorrole-----------------------------------------------------------------------Instructorrole (1 for primary instructor, 2 for secondary instructor)Note I want to make sure that each section has only one primary instructori.e. if the new instructor is assigned primary role and there is already a record in the table that has the same sectionid and instructorrole =1 on inserting the new record I should change the instructorrole to 2I will receive (a list of sectionids), instructorid, roleid I need to merge this list with the table sectioninstructorI tried another way but I got an error message matched can’t have two updatesALTER PROCEDURE [dbo].[usp_SectionInstructor_InsertList] ( @SectionList nvarchar(1000), @RoleID int,--(1 for primary and 2 for secondary) @InstructorID int )AS begin select T.items as SectionID, @InstructorID as instructorID, @RoleID as InstructorROle from dbo.fn_SP2_Split(@SectionList,',') T as Source on Target.SectionID=source.SectionID and Target.InstructorID=Source.SectionID when Matched then update set Target.InstructorRole=@RoleID, Target.updatedby=@updatedby when not matched then insert (SectionID, InstructorID, InstructorRole) values (Source.SectionID, Source.InstructorID, @RoleID );---the function--splits a list to CREATE FUNCTION [dbo].[fn_SP2_Split](@String nvarchar(max), @Delimiter nchar(1)) returns @temptable TABLE (items nvarchar(max)) as begin declare @idx int declare @slice nvarchar(250) select @idx = 1 if len(@String)<1 or @String is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0) insert into @temptable(Items) values(@slice) set @String = right(@String,len(@String) - @idx) if len(@String) = 0 break end return end Tue, 13 Nov 2012 10:34:21 GMTSarsoura