Script for incrementally creating records

问题:

Paul provided a useful script for the issue below but I would like to actually effect the changes. I can only see it if I use the select statement Please help with this one

Table name: Citizen

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

I have over 150,000 records where the Telephone number needs to be adopted to a set format (set telephone area code and in incremental order) ie 01907000001, 01907000002 as shown below. There are other columns asides the firstname and lastname which will all remain unchanged..only telephone field requires this transformation.

It should ideally look like this:

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

I will really appreciate some help or guidance on this.

回答1:

Try something like this:

SELECT 
    [FirstName],
    [LastName],
    '01907' + --area code
    RIGHT('00000' + --middle padding for zero's
        CAST(ROW_NUMBER() OVER (ORDER BY [LastName]) AS VARCHAR) --incremental value
        ,6) AS 'Telephone' --6 characters plus area code

        --<< YOUR OTHER FIELDS
FROM 
    [AdventureWorks].[Person].[Person]

I used Adventure Works just to test it.

Change the ORDER BY clause in the windowing function if you want it to increment by something else.