Someone here was kind enough to provide a script to help me generate numbers in increment so it could look like the PIN number column below. I ran the script and noticed the minimum PIN number generated is 10000 which really should be 4310
Table name: Citizen
Firstname Lastname PIN Address Age other columns....
John Smith 4310 20001 19
Andrew Evans 4311 363252 30
Bill Towny 4312 63322 67
Dame Beaut 4313 34222 34
SELECT 4310 + ROW_NUMBER() OVER(ORDER BY [All LP First Name]) NEW_PIN, *
SET [Account Reference Number] = CAST(NEW_PIN AS VARCHAR(10))
select * from Citizen order by PIN asc
SELECT MIN(PIN) FROM Citizen
your query works fine... I've tested. As @bassrek says it's your select query that has to be reviewed:
select * from Citizen order by CAST(Pin AS int)
Because Pin seems to be a
nvarchar and 10000 comes before 4.
So, you have to change pin column to int or use a cast in select.