how to update table columns with 50 string text having a specific prefix

zyfwsgd6 于 2023-02-28 发布在其他

关注(0)|答案(2)|浏览(131)

I'm trying to update two columns (UserName and FullName) of the following table: table

I want to update the table so that the UserName column has 50 values in the form of 'UN1', 'UN2', 'UN3', etc... and that the FullName column has 50 values in the form of 'FN1', 'FN2', 'FN3', etc..

However, I do not want to achieve this goal by truncating the table and then inserting this new data;
I actually want to update the data and also MAINTAIN the Id column as it is (I don't want to reseed the Id column to start from 1).

How I can do it in SQL Server?

I tried by truncating the data and then inserting the new rows but was getting the Id to start from 1 and I don't want this to happen.

sql-server

来源：https://stackoverflow.com/questions/75555549/how-to-update-table-columns-with-50-string-text-having-a-specific-prefix

2条答案

按热度按时间

0g0grzrc1#

You can simply update your data and use values provided by row_number

It's not clear from your question if you require a top clause or a where criteria but you should be able to amend the following as appropriate.

with cte as (
    select *, Row_Number() over(order by id) rn
    from t
)
update cte set
UserName = Concat('UN', rn),
FullName = Concat('FN', rn);

赞(0）回复(0）举报 2023-02-28

f0brbegy2#

what about

adding an autoinc int column "num" (thereby getting a numbering 1 to 50)
issuing something like 'UPATE table set UserName=concat("UN",CAST(num AS VARCHAR(2)))'
removing the autoinc column again

this concept should work as long as the row ids reflect the ordering that you want for the UNx and FNx

赞(0）回复(0）举报 2023-02-28

我来回答

how to update table columns with 50 string text having a specific prefix

2条答案

相关问题

热门标签

最新问答