Sql Update table

Question

Post reply

Sql Update table

Viewing 6 posts - 16 through 20 (of 20 total)

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

cmoloto-996602 SSC Veteran Points: 208 More actions · Answer 1

cmoloto-996602

SSC Veteran

Points: 208

January 26, 2010 at 5:34 am

#1108582

not an easy script but worked thanks.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

cmoloto-996602 (1/26/2010)
not an easy script but worked thanks.

It's beginner's TSQL, second day of TSQL 101 and you would benefit from spending a little time on it, which you clearly have not yet done. You will gain nothing from your course if you submit your coursework here with no intention to cooperate and learn, and you run the risk of ridicule for doing so.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

john.arnott SSChampion Points: 11882 More actions · Answer 3

Yes, Paul gave you a script that works well per your request, but consider whether you'll be able to explain the use of a CTE (Common Table Expression) should anyone ask. Also, if I may guess at the next step in this series (two so far) of exercises, how would that script behave if there are any gaps in the ID values? How would you enhance the script to use the NEXT row ordered by ID, and not just assume that it's simply one greater than the current row?

Paul White SSC Guru Points: 150467 More actions · Answer 4

john.arnott (1/26/2010)
Yes, Paul gave you a script that works well per your request, but consider whether you'll be able to explain the use of a CTE (Common Table Expression) should anyone ask. Also, if I may guess at the next step in this series (two so far) of exercises, how would that script behave if there are any gaps in the ID values? How would you enhance the script to use the NEXT row ordered by ID, and not just assume that it's simply one greater than the current row?

Heh - you're right of course. It's a (deliberately) poor script - but the only way I can see to meet the OP's exacting requirements. One can only hope that the learning process will benefit at some stage. I guess I should come clean and point out that the script I provided has a number of highly undesirable features - many of which can (and should) be corrected by the correct use of a table alias, as repeatedly pointed out by Steve and Brain Donor earlier in the thread. Leading horses to water spring to mind :laugh:

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

Considering the DROP statement, I hope the OP doesn't decide to skip the USE TEMPDB statement. 😛

--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)

Paul White SSC Guru Points: 150467 More actions · Answer 6

Jeff Moden (1/26/2010)
Considering the DROP statement, I hope the OP doesn't decide to skip the USE TEMPDB statement. 😛

Quite. I added the USE tempdb; statement just before posting actually - I had a similar fear! 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi