T-sql 2012 cursor

Transact-SQL
1

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)
begin
set @LoopCounter = @LoopCounter + 1
update [dbo].[Locker]
set lockID=@LoopCounter
FROM .[dbo].[Locker]
where [schoolID] = 134
end

2

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

1 Like
3

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

4

perhaps something like this:

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