CODEWITHSUNDEEP
sql-servert-sql
Jango
Jango

Reputation: 5545

How to update column with incremental value using tsql?

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?

Upvotes: 6

Views: 6982

Answers (3)

Yasen Zhelev
Yasen Zhelev

Reputation: 4045

For MySQL

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

Upvotes: -1

C-Pound Guru
C-Pound Guru

Reputation: 16348

Without the 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)

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 452988

;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)

Upvotes: 9

Related Questions