The Triple Update

  • Comments posted to this topic are about the item The Triple Update

  • Interesting, but I'd be wary of using it. In a multi-row update, the result is pretty much undefined, unless all the rows are updated to the same value. And in that case, there isn't much point in the approach anyway, unless you really want to confuse the reader...


    Just because you're right doesn't mean everybody else is wrong.

  • Is this behaviour specific to SQL version?  When run against my SQL 2012 environment, the variable is set to 1, but the table data remains unchanged

    Edit: Forget that. User error - it does edit the row as expected

    • This reply was modified 4 years ago by  coalesceuk. Reason: I'm an idiot
  • Interesting! Well done!

  • From the post:

    Not sure where this is useful or why it's there, but if anyone uses this, let us know.

    If you yabingle "quirky update", you'll find examples of its use - generally for doing things like running totals in the days before we had the windowing functions.

    coalesceuk wrote:

    Is this behaviour specific to SQL version?

    It's been around for aaaaages.   It certainly pre-dates SQL 2012, as it was being used in speed phreak events in 2009 and earlier...

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • nice question, thanks Steve

    been using something similar to this to build running totals (before windowing functions) for many years...

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Rune Bivrin wrote:

    Interesting, but I'd be wary of using it. In a multi-row update, the result is pretty much undefined, unless all the rows are updated to the same value. And in that case, there isn't much point in the approach anyway, unless you really want to confuse the reader...

    Actually, it can be made to work quite reliably and is a tried and true, nasty fast method for doing missing data smears and running totals.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thomas Rushton wrote:

    From the post:

    Not sure where this is useful or why it's there, but if anyone uses this, let us know.

    If you yabingle "quirky update", you'll find examples of its use - generally for doing things like running totals in the days before we had the windowing functions.

    coalesceuk wrote:

    Is this behaviour specific to SQL version?

    It's been around for aaaaages.   It certainly pre-dates SQL 2012, as it was being used in speed phreak events in 2009 and earlier...

    Heh... I wonder how many people actually got this question right without having to run the code?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Heh... I wonder how many people actually got this question right without having to run the code?

    I've been using a related technique to emulate STRING_AGG, so it wasn't that much of a leap. It's a very useful way to generate column lists from system views to use in automatically generated SQL.


    Just because you're right doesn't mean everybody else is wrong.

  • Rune Bivrin wrote:

    Jeff Moden wrote:

    Heh... I wonder how many people actually got this question right without having to run the code?

    I've been using a related technique to emulate STRING_AGG, so it wasn't that much of a leap. It's a very useful way to generate column lists from system views to use in automatically generated SQL.

    That too!  And, following prescribed methods that MS doesn't seem to know about (according to Books Online, anyway), it's quite reliable for that, missing data smears, and other running calculations such as running totals and partitioned running totals.  Like Thomas stated, it was what some people (including myself) used long before the finally got the Windowing Functions right in 2012.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Thomas Rushton wrote:

    From the post:

    Not sure where this is useful or why it's there, but if anyone uses this, let us know.

    If you yabingle "quirky update", you'll find examples of its use - generally for doing things like running totals in the days before we had the windowing functions.

    coalesceuk wrote:

    Is this behaviour specific to SQL version?

    It's been around for aaaaages.   It certainly pre-dates SQL 2012, as it was being used in speed phreak events in 2009 and earlier...

    Heh... I wonder how many people actually got this question right without having to run the code?

    I looked at it and 'knew' the answer from something at the back of my mind.  I still ran it to check though, does that count?


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Jeff Moden wrote:

    ...

    Heh... I wonder how many people actually got this question right without having to run the code?

    Even I knew about quirky update and I'm only a lowly developer. Surprised Steve wasn't familiar, guess it's a bit niche.

  • call.copse wrote:

    Jeff Moden wrote:

    ...

    Heh... I wonder how many people actually got this question right without having to run the code?

    Even I knew about quirky update and I'm only a lowly developer. Surprised Steve wasn't familiar, guess it's a bit niche.

    You, good Sir, are neither "only" nor "lowly". 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I used this behavior

    • while setting a column to the MIN/MAX() result of a subquery, where I needed this value for further use in a variable too
    • setting a column to NEWID() or NEXT VALUE OF s_sequence

    In both cases it saves you some time / code, since you have not to read the just updated table again (or assigning the variable first and use the variable in the update)

    God is real, unless declared integer.

  • This is fun code to test and try to get ones head around - in which orders are operators applied.

     

    DECLARE @i INT=0;
    UPDATE t SET @i = id = @i + 1
    SELECT @i;

    Sql Server blog: http://www.tsql.nu

Viewing 15 posts - 1 through 15 (of 18 total)

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