If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Recursive Update

Reply
 
LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 10-29-09, 07:53
dwill dwill is offline
Registered User
 
Join Date: Aug 2009
Posts: 5
Recursive Update

I think i need a recursive update to do this (to avoid cursors) - but i am not sure if I can do that in SQL and/or if in fact it's what I need to do!

The problem:

Table currently has the wrong values in a field and I need to update the entire table with the right values. The values depend on previous values in the table - like a running total for example (it's actually a conditional total but I can handle that bit). So record X depends on the new value of all records before it being updated already - hence the idea of recursion.

I currently have a cusor solution that works, but it takes a long time to run as soon as I get to 50000+ rows - and I figure that there has to be a better way. Or maybe not?

The key issue is being able to run a single update that 'knows' what the new values are. I'm not sure replicating the code would help - the idea is simple enough and the code for my real example is long in the context it is in.
Reply With Quote
  #2 (permalink)  
Old 10-29-09, 09:37
Thrasymachus Thrasymachus is offline
Button Pushing Slacker
 
Join Date: Nov 2004
Location: Arlington, VA
Posts: 6,988
in all likelihood, you can write this as a set based operation, whic would be faster but we can not help you without ddl and sample data.
__________________
software development is where smart people go to waste their lives
Reply With Quote
  #3 (permalink)  
Old 10-29-09, 10:30
blindman blindman is offline
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 10,830
Quote:
Originally Posted by dwill View Post
I think i need a recursive update to do this (to avoid cursors)
Cursors are not to be avoided blindly. In fact, they are less 'evil' than recursion in SQL and may actually be the best method to resolve your particular issue.
Recursion is NOT the method I would have chosen for this.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On