Script to update a column(更新列的脚本)
问题描述
表名: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 JOIN
s 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
.
这篇关于更新列的脚本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:更新列的脚本


基础教程推荐
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01