How to update column with incremental value using tsql?

Question

I wanna update first and last name of one table with incremental value. For example:-

ID   FirstName  
1    Demo_01    
2.   Demo_02
3.   Demo_03
4.   And so on....

This table has 1000s of records. But for demo purposes we do not want to share real Name. So Please help how to update First Name with "Demo_ + Incremental value by one?

Answer 1

;with cte as
(
SELECT FirstName, ROW_NUMBER() OVER (ORDER BY ID) RN
FROM YourTable
)

UPDATE cte
SET FirstName = 'Demo_ ' + CAST(RN AS VARCHAR(10))

Or do you mean you want to use the ID field directly?

UPDATE YourTable
SET FirstName = 'Demo_ ' + CAST(ID AS VARCHAR(10))

NB: You say you have thousands of records but obviously they won't fit in the Demo_01 format. Say you want to allow up to 6 digits and pad with leading zeroes you could use something like.

UPDATE YourTable
SET FirstName = 'Demo_ ' + RIGHT('000000' + CAST(ID AS VARCHAR(6)),6)