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