I have a Users Table

It is full of users already and I would like to start using the UserPIN in the software (this is an nvarchar column).

I would like to update the UserPIN column with the row_number.

All of my efforts have resulted in setting the UserPIN to 1 for every record.  I just want an update query that fill the UserPIN column in sequential order.

Thanks

;with cte as (select *, row_number() over (order by UserPK) as Rn from Users)

update cte set UserPin = convert(nvarchar(30), Rn) -- if you want to add 0s at the beginning use

right(replicate('0', 30) + convert(nvarchar(30), Rn), 30) 

Hi,

Can you please post the TABLE structure? What is the Primary key. WHEN you say 'FULL OF users', how big is the TABLE. ALL this need to be considered before deciding the best approach.

for now, USE a CTE OR temp TABLE AND THEN populate the records WITH a query that has 'Row_number' ranking function. Later UPDATE your TABLE BY joining either WITH CTE OR temp table.

If the volume is very large, do it in chunks.

• Proposed as answer by Naomi NModerator 9 hours 7 minutes ago

>> I have a Users Table << 

Where is the DDL? Where is the sample data? 

>>It is full of users already and I would like to start using the user_pin in the software (this is an nvarchar column). << 

Where is the DDL? What the constraints on this user_pin column? Your narrative tells us that it can be a 500 Chinese character string! Or maybe NULL.  

This is why minimal polite behavior on an SQL forum is to post DDL for the last 30 years. Did you read the front of the forum before posting? 

>> I would like to update the user_pin column with the ROW_NUMBER().  All of my efforts have resulted in setting the user_pin to 1 for every record [sic]. <<

Rows are not records. ROW_NUMBER() OVER(..) is function. 

>> I just want an update query [sic] that fill the user_pin column in sequential order. <<

An UPDATE is a statement and not a query. Another fundamental concept of RDBMS. 

How secure do you think that this will make your table? Why do you think that it is hard to guess a sequential perosnal identification number? And why did you fail to show us any effort you made? 

Google CREATE SEQUENCE, if you are determined to destroy user security. Otherwise, look up additive congruence  generators. They produce pseudo-random series of values that does not repeat. 

This is why minimal polite behavior on an SQL forum is to post DDL for the last 30 years. Did you read the front of the forum before posting? 

Where can I find this mythical 'front of the forum' you keep referring to?

May be this one https://social.msdn.microsoft.com/Forums/en-US/8e38b637-2a1e-4681-8937-337372b4d0fe/posting-tips-code-images-hyperlinks-details?forum=transactsql but it seems to be missing a request for DDL. May be I should add it.

Update. I went ahead and added a note abou