The Triple Update

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720983

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

  • Rune Bivrin

    SSCertifiable

    Points: 7860

    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.

  • coalesceuk

    SSC Eights!

    Points: 860

    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 6 months, 2 weeks ago by  coalesceuk. Reason: I'm an idiot
  • Carlo Romagnano

    SSC-Insane

    Points: 22011

    Interesting! Well done!

  • Thomas Rushton

    SSC-Insane

    Points: 22648

    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

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71915

    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”

  • Jeff Moden

    SSC Guru

    Points: 997169

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden

    SSC Guru

    Points: 997169

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Rune Bivrin

    SSCertifiable

    Points: 7860

    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.

  • Jeff Moden

    SSC Guru

    Points: 997169

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Neil Burton

    SSC-Insane

    Points: 22254

    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

  • call.copse

    SSCoach

    Points: 17242

    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.

  • Jeff Moden

    SSC Guru

    Points: 997169

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Thomas Franz

    Hall of Fame

    Points: 3715

    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)

    • This reply was modified 6 months, 2 weeks ago by  Thomas Franz.

    God is real, unless declared integer.

  • Magnus Ahlkvist

    SSC Eights!

    Points: 978

    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