Problem with update statement

  • Please try to run example below to understand the problem.

    I am trying to update val1 and val2 columns based on join of table 1 and table 2 on id column.

    When values are updated for both column, I see that first val is updated from first row and second value is updated from another row. Both val1 and val2 should either be updated from first row or second row. but that is not the case.

    when Sql Server see null value in any column, it picks next value which is not null. but thats wrong as now I am getting data from two different rows.

    Can someone explain why this is happening and how to avoid this?

    Try to run below code and see the problem.

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

    create table #temp

    (

    id int,

    val1 int,

    val2 int

    )

    create table #temp2

    (

    id int,

    val1 int,

    val2 int

    )

    insert into #temp (id, val1) values (1, 1)

    insert into #temp values (1, 2 ,3)

    insert into #temp2 values (1 , 8 , 8)

    select * from #temp

    --id val1 val2

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

    --1 1 NULL

    --1 2 3

    select * from #temp2

    --id val1 val2

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

    --1 8 8

    update a

    set a.val1 = b.val1,

    a.val2 = b.val2

    from#temp2 a

    join#temp b

    ona.id = b.id

    select * from #temp2

    --id val1 val2

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

    --1 1 3

    drop table #temp

    drop table #temp2

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

  • CoolDown (3/1/2012)


    I am trying to update val1 and val2 columns based on join of table 1 and table 2 on id column.

    When values are updated for both column, I see that first val is updated from first row and second value is updated from another row. Both val1 and val2 should either be updated from first row or second row. but that is not the case.

    when Sql Server see null value in any column, it picks next value which is not null. but thats wrong as now I am getting data from two different rows.

    I actually quite dint get what your requirements are. Can you post your expected output and the rules for the query?

  • Source Table1

    idval1val2

    11NULL

    123

    Destination Table before update

    idval3val4

    188

    Expected Destination Table after update - Option 1

    idval3val4

    11NULL

    Expected Destination Table after update - Option 2

    idval3val4

    123

    Destination Table after update - Real Output

    idval3val4

    113<-- This should be null

  • Quite peculiar behavior indeed!

    By using a RIGHT JOIN you can make it return option 1. Try it with the OUTPUT statement I've included and you'll see that it seems to only UPDATE the record once.

    update a

    set a.val1 = b.val1,

    a.val2 = b.val2

    OUTPUT inserted.*, DELETED.*

    from #temp2 a

    RIGHT join #temp b

    on a.id = b.id

    I found that INNER JOIN, LEFT OUTER JOIN both return the same result. RIGHT JOIN and FULL OUTER JOIN also return the same result (option 1).


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • you can check this more real example too.

    create table #Data

    (

    COMPANY int,

    NAMEvarchar(50),

    SALARY int

    )

    create table #Random

    (

    Random_COMPANY int,

    Random_NAMEvarchar(50),

    Random_SALARY int

    )

    insert into #Data (COMPANY, NAME) values (1, 'ABC' )

    insert into #Data values (1, 'XYZ', 25000 )

    insert into #Random(Random_COMPANY) values (1 )

    select * from #Data

    select * from #Random

    update a

    set a.Random_NAME = b.NAME,

    a.Random_SALARY = b.SALARY

    from#Random a

    join#Data b

    ona.Random_COMPANY = b.COMPANY

    select * from #Random

    drop table #Data

    drop table #Random

    output

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

    Source Table1

    COMPANYNAME SALARY

    1 ABC NULL

    1 XYZ 25000

    Destination Table before update

    Random_COMPANYRandom_NAMERandom_SALARY

    1 NULL NULL

    Expected Destination Table after update - Option 1

    Random_COMPANYRandom_NAMERandom_SALARY

    1 ABC NULL

    Expected Destination Table after update - Option 2

    Random_COMPANYRandom_NAMERandom_SALARY

    1 XYZ 25000

    Destination Table after update

    Random_COMPANYRandom_NAMERandom_SALARY

    1 ABC 25000<-- This should be null

  • This article seems to be relevant:

    http://www.sql-questions.com/microsoft/SQL-Server-Programming/34302827/how-to-update-same-row-more-than-once-in-one-update-statement.aspx

    Haven't found a way to return your option 2!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • something like this, may be?

    -- Open a tran

    BEGIN TRAN

    -- Choose data before update

    SELECT * FROM #temp2

    UPDATE t2

    SET t2.val1 = tCrsApp.val1

    , t2.val2 = tCrsApp.val2

    FROM #temp2 t2

    CROSS APPLY

    ( -- This will generate a random

    -- row from the #temp table

    SELECT TOP 1 *

    FROM #temp t1

    WHERE t1.id = t2.id

    ORDER BY NEWID()

    ) tCrsApp

    -- Data after update

    SELECT * FROM #temp2

    -- Rollback/commit as per the requirement

    ROLLBACK TRAN

    {Edit - Added comments to the code}

  • I was going to suggest this (similar to ColdCoffee):

    update a

    set @val1 = a.val1 = b.val1,

    @val2 = a.val2 = b.val2

    OUTPUT inserted.*, DELETED.*

    from #temp2 a

    join (

    SELECT TOP 1 * FROM #temp ORDER BY val2 DESC) b

    on a.id = b.id

    If you have some field, like a DATETIME you can order the #temp table on that controls which record you want applied, you should be set.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Thanks!!

    I am trying to understand why SQL Server is updating records using two different rows? My thinking was in any case it gets all columns updated from one row.

Viewing 9 posts - 1 through 8 (of 8 total)

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