問題描述

表名:Citizen

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

理想情況下應(yīng)該是這樣的:

It should ideally look like this:

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

早些時(shí)候，有人能夠使用 select 語句提供腳本.增量創(chuàng)建記錄的腳本

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

然而，我希望實(shí)施更改，因?yàn)槲抑荒茉谑褂蒙鲜鲞x擇腳本時(shí)查看它們.

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

推薦答案

為此不需要 JOIN.您可以使用添加的新列的值直接從 CTE 更新.我假設(shè)您希望將 Telephone1 列設(shè)置為等于查詢的計(jì)算 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

這會(huì)將您的 citizen 表的 Telephone1 列更新為計(jì)算出的 NewTelephone1 的值.

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

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