Incremental value by group of 9 rows

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Incremental value by group of 9 rows

Author

Topic

gsql
Starting Member

1 Post

Posted - 2011-06-23 : 11:48:05

Good day all. I'm new to sql and trying to update a column with an incremental value by 1, every 9 rows.

example
1
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
etc.

This is what I have so far but I think I'm using the update wrong.

declare @fieldblock as int,
@Counter as Int

Set @FieldBlock = 1

While @FieldBlock <=155
Begin
Set @Counter = 1
While @Counter <=9
Begin
Print @FieldBlock
Update FieldDetail
Set Record = @FieldBlock
Where field like '%test%'
Set @Counter = @Counter + 1
End
Set @FieldBlock = @Fieldblock + 1
End

I've been dreaming about how to solve this for a few days. Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-23 : 11:58:50

To be able to do this, you need some way to order the rows in the table. By definition, there is no ordering to data in a table. Suppose you had a column which contained a sequence number starting at 1 through number of rows. Then, you can update your new column in a single set based query like this:


update YourTable set Record =  (SeqNum-1)/9+1

Here SeqNum is your sequence number column that starts at 1. Do you have anything like that in your data? If not, is there a way to order the data in the table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources