question

Murali avatar image
Murali asked

how to update

In a table...i have rows abount 90....and the table is having a column called rownumber....this rownumber i need to update like ....till 30 rows i need to update with 1 to 30 and again from 31 to 60...again it should update with 1 to 30....like for every 30 rows...the number series should re-start from 1.
t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
If you have all the rows numbered in a way you have mentioned, then you can use a simple update. UPDATE aTable SET Rownumber = (Rownumber - 1) % 30 + 1 or simply use a select to see the results prior updating the rows SELECT (Rownumber - 1) % 30 + 1 FROM aTable In case you have gaps in the numbering or eg. want to renumber the rows in different way, then on SQL Server 2005 and above you can play nicely with the ROW_NUMBER() function. E.g. as example belows generates a table with numbered rows and then renumbers the original numbers in reverse order in partitions of 30 rows. DECLARE @tbl TABLE ( Rownumber int, UpdatedRowNo int ) INSERT INTO @tbl (Rownumber) SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns ;WITH DataRows AS ( SELECT Rownumber, UpdatedRowNo, ROW_NUMBER() OVER(ORDER BY Rownumber DESC) AS NewRowNumber FROM @tbl ) UPDATE DataRows SET UpdatedRowNo = (NewRowNumber - 1) % 30 + 1 SELECT * FROM @tbl ORDER BY Rownumber DESC
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thank you for your sample example...it is perfectly generating row number for every 30 rows. In addition to that, as per my requirement, in a table..i have a material group column...for each material group ...for every 30 rows...row number should restart. When ever the materialgroup is changing, then row number needs to start from 1.
0 Likes 0 ·
On SQL Server 2005 you can use the ROW_NUMBER() function.. As in my example. If you need to restart the numbering by every material group then it will look like: SELECT ROW_NUMBER() OVER(PARTITION BY MaterialGroup ORDER BY OrderColumn) As RowNo FROM aTable This will generate numbers from 1 on every change in Material Group and the number will be in the order of the OrderColumn. If you put your table metadata the sample can be closer.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.