更新列的脚本

表名：公民

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

理想情况下它应该是这样的：

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

早些时候，有人能够使用 select 语句提供一个脚本。用于增量创建记录的脚本 https://stackoverflow.com/questions/39348200/scipt-for-inreamentally-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

不过，我希望实施这些更改，因为只有使用上面的选择脚本才能查看它们。

No JOIN为此需要 s。您可以直接从CTE使用添加的新列的值。我假设你想设置Telephone1列等于计算的Telephone1您的查询的列。这是这个的代码：

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