Bad results from 3 part update

  • I recently finished reading this article wrote by Mr Jeff Moden.

    http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]

    We wants to add "Multiples of 3" in our table like 3, 6, 9, 12. So we made a table for this goal with this structure:

    create TABLE temp

    (RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, SomeInt INT, Expected INT)

    INSERT INTO temp (SomeInt, Expected)

    SELECT 0,3 UNION ALL SELECT 0,6 UNION ALL SELECT 0,9 UNION ALL SELECT 0,12

    Now we insert 3, 6, 9, 12 into our table with this query without any problem:

    DECLARE @N INT, @Anchor INT

    SELECT @N = 0

    UPDATE temp

    SET @N = SomeInt = @N + 3, --Adds 3 to N and updates SomeInt with N

    @Anchor = RowNum

    FROM temp

    OPTION (MAXDOP 1)

    But this query return unexpected results:

    DECLARE @N INT, @Anchor INT

    SELECT @N = 0

    UPDATE temp

    SET @N = @N + 1, --Adds 1 to N

    @N = SomeInt = @N + 2, --"Forgets" to do @N + 2 after first row

    @Anchor = RowNum

    FROM temp

    OPTION (MAXDOP 1)

    Jeff Moden mentioned "There's a very long winded explanation about this that I'll very gratiously step aside".

    And also he mentioned "YOU MUST HAVE AN "ANCHOR" COLUMN" like @Anchor = RowNum. He also said "there are some unpredictable cases where an error can occur without anchor"

    Now I want to know why? I want to know the exact reasons. I want to realize it logically.

    What makes 3 part update sometimes don't return appropriate results?

    Also why I have to use an anchor in my query while logically there is no need of it?

    Warmly thank you for your time and help

    ___________________________________
    Computer Enterprise Masoud Keshavarz
    I don't care about hell.
    If I go there I've played enough Diablo to know how to fight my way out.

Viewing 0 posts

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