Efficient SQL wanted!! 2

[ColinM]

I have a table roughly like this:

ID RecNo
1 1
2 Null
3 Null
4 1
5 Null
6 2
7 Null
8 2

What I would like to do is change the table to:

ID RecNo
1 1
2 1
3 1
4 1
5 Null
6 2
7 2
8 2

i.e. fill in the blanks between similar numbers.

Can I do this with one good sql statement? Rather than a cursor or a loop as this would require thousands of separate updates.

I thought maybe using something vaguely along the line of the procedure in the faq could be used?

declare @variable int
set @variable = 0
update table
SET @variable = column = @variable + 1

 

[FowlerConsulting]

Will need the cursor because SQL can not evauate across records... you could do somthing with rowid, but I would stay away from this.
Steven Fowler
steven_m_fowler@yahoo.com

http://www.fowlerconsulting.com

 

[jnicho02]

Colin,
Could you do something like;
[tt]declare @variable int;
update table
[tab]SET @variable = column = isnull(column,@variable);
[/tt]

i.e. store the previous value in @variable, if the current row has a null replace it with @variable, otherwise keep it as it is. Then store the current value in @variable.

However, this would set row 5 to '1' and not keep it as null. Doh!

p.s. I haven't actually tested that bit of code as I am at work and don't have SQL Server here.... My Home -->

http://www.geocities.com/jnicho02/

 

[ColinM]

Excellent!
Thats close enough for me
Certainly a lot faster than what I was using.
I can work without the nulls anyway.
Thanks!