The Triple Update

Question

The Triple Update

Steve Jones - SSC Editor

SSC Guru

Points: 736173
More actions
March 10, 2020 at 12:00 am

#3730290

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

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

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

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 1

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: 863 More actions · Answer 2

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, 8 months ago by coalesceuk. Reason: I'm an idiot

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 3

Carlo Romagnano

SSC-Insane

Points: 22668

March 10, 2020 at 8:37 am

#3732719

Interesting! Well done!

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 4

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: 72624 More actions · Answer 5

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: 1004324 More actions · Answer 6

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)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

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)

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 8

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: 1004324 More actions · Answer 9

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)

Neil Burton SSC-Insane Points: 22999 More actions · Answer 10

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: 18050 More actions · Answer 11

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: 1004324 More actions · Answer 12

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)

Thomas Franz Hall of Fame Points: 3893 More actions · Answer 13

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 4 years, 8 months ago by Thomas Franz.

God is real, unless declared integer.

Magnus Ahlkvist Ten Centuries Points: 1000 More actions · Answer 14

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