如何使用 SQL Server 中的替换更新多个列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1169078/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I update multiple columns with a Replace in SQL server?
提问by Hcabnettek
How do I update different columns and rows across a table? I want to do something similiar to replace a string in SQL server
如何更新表中的不同列和行？我想做一些类似的事情来替换 SQL 服务器中的字符串
I want to do this but the value exists in multiple columns of the same type. The values are foreign keys varchars to an employee table. Each column represents a task, so the same employee may be assigned to several tasks in a record and those tasks will vary between records. How can I do this effectively? Basically something of a replace all accross varying columns throughout a table.
我想这样做，但该值存在于多个相同类型的列中。这些值是员工表的外键 varchars。每列代表一个任务，因此同一员工可能被分配到记录中的多个任务，而这些任务在记录之间会有所不同。我怎样才能有效地做到这一点？基本上是替换整个表中不同列的所有内容。
Thanks for any help or advice.
感谢您的任何帮助或建议。
Cheers, ~ck in San Diego
干杯，~在圣地亚哥
回答by Lance Roberts
This should do the trick:
这应该可以解决问题：
UPDATE table1
SET field1 = replace(field1, 'oldstring', 'newstring'),
field2 = replace(field2, 'oldstring2', 'newstring2')
etc...
等等...
回答by Ray
For SQL Server 2005 this should do the trick:
对于 SQL Server 2005，这应该可以解决问题：
http://www.mssqltips.com/tip.asp?tip=1555
http://www.mssqltips.com/tip.asp?tip=1555
Allows searching and replacing across all columns across all tables. Make sure you read the article though.
允许在所有表的所有列中进行搜索和替换。确保你阅读了这篇文章。
回答by Daniel Corzo
The main idea is to create a SQL Update sentence, no matter how many fields has the table. It was created on SQL Server 2012, however I think it works on 2008 too.
主要思想是创建一个SQL Update语句，不管表有多少个字段。它是在 SQL Server 2012 上创建的，但我认为它也适用于 2008。
Sample table:
示例表：
CREATE TABLE SampleTable
(
Field1 INT,
Field2 VARCHAR(20),
Field3 VARCHAR(20),
Field4 VARCHAR(100),
Field5 DATETIME,
Field6 NVARCHAR(10)
);
Get only varchar and nvarchar fields. Change OLD_TEXTand NEW_TEXTaccord to your requirement. Change system_type_idvalues if you need match not only varchar and nvarchar fields.
仅获取 varchar 和 nvarchar 字段。根据您的要求更改OLD_TEXT和NEW_TEXT。如果您不仅需要匹配 varchar 和 nvarchar 字段，请更改system_type_id值。
SELECT 'UPDATE dbo.SampleTable SET ' + STUFF((SELECT ', [' + name + '] = REPLACE([' + name + '], ''OLD_TEXT'', ''NEW_TEXT'')'
FROM sys.COLUMNS
WHERE
[OBJECT_ID] = OBJECT_ID('SampleTable')
AND [is_identity] = 0 --It's not identity field
AND [system_type_id] in (167, 231) -- varchar, nvarchar
FOR XML PATH('')), 1,1, '')
The result of the last query is:
最后查询的结果是：
UPDATE dbo.SampleTable SET
[Field2] = REPLACE([Field2], 'OLD_TEXT', 'NEW_TEXT'),
[Field3] = REPLACE([Field3], 'OLD_TEXT', 'NEW_TEXT'),
[Field4] = REPLACE([Field4], 'OLD_TEXT', 'NEW_TEXT'),
[Field6] = REPLACE([Field6], 'OLD_TEXT', 'NEW_TEXT');
just copy the result and execute in SSMS. This snippet saves you a little time when writing the update sentence.
只需复制结果并在 SSMS 中执行。在编写更新语句时，此代码段可为您节省一些时间。
Hope it helps.
希望能帮助到你。
回答by Jason Kester
In answer to the poster's supplementary question about how to normalize this data structure. Here's how you'd do it:
回答海报关于如何规范化此数据结构的补充问题。这是你的方法：
Project
-------
ProjectID
ProjectName
etc...
Employee
--------
EmployeeID
EmployeeName
etc...
Task
----
TaskID
ProjectID
EmployeeID
TaskDescription
etc...
Your current structure, where you have a bunch of Task1, Task2, etc... columns in the Project table, was clearly not designed by somebody that understands relational databases.
您当前的结构，其中在 Project 表中有一堆 Task1、Task2 等...列，显然不是由了解关系数据库的人设计的。
During the process of firing that individual, you might explain that his design violates the First Normal Form, while directing him to the "Repeating groups across columns"section of that linked article.
在解雇那个人的过程中，您可以解释他的设计违反了第一范式，同时将他引导到该链接文章的“跨列重复组”部分。
