Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
duncanwill
Starting Member
20 Posts |
 Posted - 2009-10-29 : 07:40:18
|
| 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. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-10-29 : 14:21:51
|
if you have an identity column (with no holes in it) you can join the table to itselfFROM myTable t1JOIN myTable t2On t1.id = t2.id -1 |
 |
|
|
|
|
|
Recursive Update on table