Post reply

In case of multiple matches for a key in a join , which value is updated ?

  • November 2, 2012 at 1:59 am

    #266505

    HI All

    Below is the scenario :

    create table #test2 (j int,i int ,k char(1) )

    create table #test3 (i int, k char(1))

    insert into #test1 select 1

    insert into #test2 select 1,1,'a'

    insert into #test2 select 2,1,'b'

    insert into #test2 select 3,1,'c'

    insert into #test2 select 4,1,'d'

    insert into #test3 (i) select 1

    update #test3

    set k = #test2.k

    from #test3 inner join #test2

    on #test3.i = #test2.i

    Now , in this update statement, what I see is ,#test3.k is set to 'a' that is the first match .

    Is it always the first match ? or its randomly picked by SQL Server ? Is there any algorithm for it ?

  • November 2, 2012 at 2:22 am

    #1555310

    return First Match.

  • November 2, 2012 at 2:22 am

    #1555311

    Whichever row results from the exec plan generated for the update. This is a scenario you should be very careful to avoid when writing update statements

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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