問題描述

表名: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.

這篇關于更新列的腳本的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！