merge a result set with a table but update according to existing values in target

  • I have a table sectioninstructor

    Sectionid,

    Instructorid,

    Instructorrole

    -----------------------------------------------------------------------

    Instructorrole (1 for primary instructor, 2 for secondary instructor)

    Note I want to make sure that each section has only one primary instructor

    i.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 2

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

    ALTER 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

  • This was removed by the editor as SPAM

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply