问题描述
表名: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.