如何使用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?

该表有1000个记录。但出于演示目的,我们不想共享真实姓名。那么请帮助如何使用“Demo_ +增量值”更新名字?

3 个解决方案

#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?

或者你的意思是你想直接使用ID字段?

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.

注意:你说你有成千上万的记录,但显然它们不适合Demo_01格式。假设你想允许最多6个数字并使用前导零填充你可以使用类似的东西。

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

#2

Without the CTE:

没有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)

#3

For MySQL

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

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