How to update a column with incremental value using tsql?

Question

I want to update the name and surname of one table with an extra value. For instance: -

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

This table contains 1000 entries. But for demonstration purposes, we do not want to share a real name. So, please help, how to update the name using "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 want you to use the ID field directly?

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

NB: You say that you have thousands of records, but obviously they will not fit into the Demo_01 format. Suppose you want to allow up to 6 digits and fill with leading zeros, you can use something like.

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

Answer 2

Without CTE:

 DECLARE @NewID INT; SET @NewID = 0; UPDATE myTable SET @NewID = ID = @NewID + 1 UPDATE myTable SET FirstName = 'Demo_ ' + RIGHT('000000' + CAST(ID AS VARCHAR(6)),6) 

Answer 3

For MySQL

 UPDATE tablename SET FirstName = CONCAT('Demo_',ID);