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