Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2009

    Unanswered: 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.

  2. #2
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    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 "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts