update using self-join - confirm it looks at old values

  • I'm doing an update using a self-join, and just want to confirm something.  If the query updates a certain row I'll call row A, and then updates another row B based on a value in row A, does it look at the old value in row A, or the new value?

    Since we can't do an order by in an update statement, there would be no way to know what order the rows are updated in, so I assume it would always look at the old value.  Is there maybe an official Microsoft page explaining this?

  • The old value.  The new value can't be seen by a transaction modifying that table until the transaction's been committed.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Makes sense- thanks.

  • OK, technically, other queries in the same transaction that did the UPDATE could see it before committing it, in something like this:

    BEGIN TRANS

    UPDATE

    SELECT

    COMMIT TRANS

    But not within the context of a single UPDATE.

    So, as you suspected, the answer is still the same: the UPDATE query can only see the old values.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Here is an illustration that may help.    Note that both columns don't wind up with the original value of V2 until the update   ends (commits).

    declare @table table (V1 int, V2 int)
    insert into @table values (1,2), (3,4), (5,6)

    select * from @table

    update @table
    set V1 = V2
    , V2 = V1

    select * from @Table

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • BUT, within the context of the same transaction you see the changes prior to the commit.

    (Used a temp table here because table variables,  like all variables, retain their current values despite ROLLBACK.)

    create table #table (V1 int, V2 int)
    insert into #table values (1,2), (3,4), (5,6)

    select * from #table

    begin transaction

    update #table
    set V1 = V2
    , V2 = V1

    select * from #Table -- changes visible within context of open transaction

    rollback -- changes do not get committed

    select * from #Table


    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

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