I 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 sectioninstructor

I 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

The 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.

____________________________________________Space, the final frontier? not any more...All limits henceforth are self-imposed.“libera tute vulgaris ex”