SQLTeam.com | Weblogs | Forums

T-sql 2012 cursor


In a t-sql 2012 listed below, I want the lockids to be set individually from 117173 to 117678. The problem with the sql listed below is all the lockid values end of with the values of 117678. Thus can you show me the sql on how to get each row value set to a unique value?

declare @LoopCounter int = 117172
While (@LoopCounter <= 117678)
set @LoopCounter = @LoopCounter + 1
update [dbo].[Locker]
set lockID=@LoopCounter
FROM .[dbo].[Locker]
where [schoolID] = 134


if I understand your requirement correctly this is what I think you need:

update X
set lockID=117678
FROM .[dbo].[Locker] AS X
WHERE lockID = 117173
-- Don't know if you also need this? AND [schoolID] = 134 

however, I can also read your request that you want to change all the [lockID] values for all rows in [Locker] where [schoolID] = 134 to be sequentially numbered from 117173 to 117678 ...

... or maybe something else altogether?!! in which case pleas clarify the requirement


I want each lockid to be unique. I do not want each lockid to have the same values.


perhaps something like this:

	SELECT	[T_RowNumber] = ROW_NUMBER() + 117173 - 1
				PARTITION BY schoolID 
				ORDER BY schoolID	-- Add tie-break columns for the sequence of [lockID], if required
	FROM	dbo.[Locker] AS X
	WHERE 	[schoolID] = 134
SET	lockID = T_RowNumber