Script to update a column

问题:

Table name: 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   

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.

回答1:

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

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

回答2:

You'll need to use a CTE for the windowing function. Like this:

WITH cte AS
    (
    SELECT
        [All Liable Partys PIN], 
        '01907' + RIGHT('00000' + CAST(ROW_NUMBER() OVER (ORDER BY [LastName]) AS VARCHAR), 6) AS 'NewTelephone1'
    FROM
        [citizen]
    )

UPDATE
    c
SET
    c.[Telephone1] = cte.[NewTelephone1]
FROM
    [citizen] c
    JOIN cte
        ON c.[All Liable Partys PIN] = cte.[All Liable Partys PIN]

I'm making an assumption here that we can use the PIN field to join on.

回答3:

UPDATE c
SET c.[Telephone] = '0' + CAST(1907000000 + t.RowNumber AS VARCHAR)
FROM Citizen c
INNER JOIN
(
    SELECT [First Name], 
           [Last Name], 
           ROW_NUMBER() OVER (ORDER BY [Last Name], [First Name]) AS RowNumber
    FROM Citizen
) t
    ON c.[First Name] = t.[First Name] AND
       c.[Last Name]  = t.[Last Name]