更新列的脚本

2021-09-10 00:00:00 tsql sql-server

表名:Citizen

Firstname       Lastname    Telephone1         Many other columns......
John             Smith      03907625212    
Andrew           Evans      0807452132    
Bill             Towny      05907122139  
Dame             Beaut      07894650569

理想情况下应该是这样的:

It should ideally look like this:

Firstname       Lastname      Telephone1         Many other columns......
John             Smith       01907000001   
Andrew           Evans       01907000002  
Bill             Towny       01907000003 
Dame             Beaut       01907000004

早些时候，有人能够使用 select 语句提供脚本.增量创建记录的脚本

Earlier, someone was able to kindly provide a script using the select statement. Script for incrementally creating records

select [First Name], 
       [Last Name], 
       [All LP Surname], 
       [All Liable Partys PIN], 
       '01907' + RIGHT('00000' + CAST(ROW_NUMBER() OVER (ORDER BY [Last Name]) AS VARCHAR), 6) AS 'Telephone1', 
       [Telephone2], 
       [Mobilephone], 
       [EmailAddress] 
FROM citizen

然而，我希望实施更改，因为我只能在使用上述选择脚本时查看它们.

I would however like the changes to be implemented as I can only view them if I use the above select script.

推荐答案

为此不需要 JOIN.您可以使用添加的新列的值直接从 CTE 更新.我假设您希望将 Telephone1 列设置为等于查询的计算 Telephone1 列.代码如下:

No JOINs are required for this. You can update directly from a CTE using the value of the new column added. I'm assuming you're wanting to set the Telephone1 column equal to the computed Telephone1 column of your query. Here's the code for this:

;With ToUpdate As
(
    Select *,
           '01907' + 
               Right('00000' + 
                   Cast(Row_Number() Over (Order By [Last Name]) As Varchar)
                , 6) 
           As NewTelephone1
    From   Citizen
)
Update  ToUpdate
Set     Telephone1 = NewTelephone1

这会将您的 citizen 表的 Telephone1 列更新为计算出的 NewTelephone1 的值.

This will update your citizen table's Telephone1 columns to be the values of the computed NewTelephone1.

相关文章