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