如何在 SQL Server 中使用替换更新多个列？

Question

如何更新表中的不同列和行？ 我想做一些类似于 替换字符串在 SQL Server 中

我想这样做，但该值存在于同一类型的多个列中。 这些值是员工表的外键 varchar。 每列代表一个任务，因此同一员工可能会被分配到记录中的多个任务，并且这些任务在记录之间会有所不同。 我怎样才能有效地做到这一点？ 基本上是替换整个表中不同列的所有内容。

感谢您的任何帮助或建议。

干杯， ~ck 在圣地亚哥

Answer 1

这应该可以解决问题：

UPDATE table1
SET field1 = replace(field1, 'oldstring', 'newstring'),
    field2 = replace(field2, 'oldstring2', 'newstring2')

等等...

Answer 2

主要思想是创建一个SQL Update语句，无论表有多少个字段。 它是在 SQL Server 2012 上创建的，但我认为它也可以在 2008 上运行。

示例表：

CREATE TABLE SampleTable
(
    Field1 INT,
    Field2 VARCHAR(20),
    Field3 VARCHAR(20),
    Field4 VARCHAR(100),
    Field5 DATETIME,
    Field6 NVARCHAR(10)
);

仅获取 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, '')

最后一次查询的结果是：

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');

只需复制结果并在SSMS中执行即可。 这个片段可以为您在编写更新语句时节省一些时间。

希望能帮助到你。

Answer 3

回答发帖者关于如何标准化此数据结构的补充问题。 这样做的方法如下：

Project
-------
ProjectID
ProjectName
etc...

Employee
--------
EmployeeID
EmployeeName
etc...

Task
----
TaskID
ProjectID
EmployeeID
TaskDescription
etc...

您当前的结构（项目表中有一堆 Task1、Task2 等...列）显然不是由了解关系数据库的人设计的。

在解雇该人的过程中，您可能会解释说他的设计违反了第一范式，同时将他定向到该链接文章的“跨列重复组”部分。

Answer 4

我知道已经过去 12 年了，但我最近有一个类似的需求，这就是我解决它的方法（在没有运气在任何地方找到合适的完整解决方案之后）。 不同之处在于，我必须更改包含特定字符串值的所有数据库表中的值。 以下过程更为通用，也可用于一张表。

CREATE OR ALTER PROCEDURE UPDATE_ALL_COLUMNS
    @TableNameSearchFilter NVARCHAR(100),
    @TableSchema NVARCHAR(100),
    @TestValue NVARCHAR(100),
    @NewValue NVARCHAR(100)
AS
BEGIN
    
    DECLARE @NRCOLUMNS INT;
    DECLARE @i INT = 0;
    DECLARE @COLUMN NVARCHAR(100) = '';
    DECLARE @SQL NVARCHAR(MAX) = '';
    DECLARE @TableToUpdate NVARCHAR(256) = '';
    DECLARE @insertingNULL BIT;

    IF (@NewValue IS NULL) SET @insertingNULL = 1
    ELSE SET @insertingNULL = 0;

    WHILE @TableToUpdate IS NOT NULL
    BEGIN
        SELECT @TableToUpdate = MIN(TABLE_NAME)
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME LIKE @TableNameSearchFilter
            AND TABLE_SCHEMA = @TableSchema
            AND TABLE_NAME > @TableToUpdate;
            
        WITH CTE1 AS
        (
            SELECT ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) AS RN
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = @TableToUpdate
                AND TABLE_SCHEMA = @TableSchema                     
                AND (@insertingNULL = 0 OR (@insertingNULL = 1 AND IS_NULLABLE = 'YES'))
        )
        SELECT @i = MIN(RN), @NRCOLUMNS = MAX(RN) FROM CTE1;

        WHILE (@i <= @NRCOLUMNS AND @TableToUpdate IS NOT NULL)
        BEGIN
            WITH CTE AS
            (
                SELECT *, ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) AS RN
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_NAME = @TableToUpdate
                    AND TABLE_SCHEMA = @TableSchema                     
                    AND (@insertingNULL = 0 OR (@insertingNULL = 1 AND IS_NULLABLE = 'YES'))
            )
            SELECT @COLUMN = COLUMN_NAME 
            FROM CTE
            WHERE RN = @i;

            SET @SQL = @SQL + 
                N'UPDATE D SET ' + @COLUMN + N' = ' + ISNULL(N'''' + @NewValue + N'''', N'NULL')
                + N' FROM ' + @TableSchema + N'.' + @TableToUpdate + N' D WHERE CAST(D.' + @COLUMN + ' AS NVARCHAR) = ' + ISNULL(N'''' + @TestValue + N'''', N'NULL') + ';'
                + NCHAR(13) + NCHAR(10);

            SET @i = @i + 1;
        END;        
    END;    

    --PRINT SUBSTRING(@SQL, 1, 4000)
    --PRINT SUBSTRING(@SQL, 4001, 8000)
    --PRINT SUBSTRING(@SQL, 8001, 12000)
    --PRINT SUBSTRING(@SQL, 12001, 16000)
    --PRINT SUBSTRING(@SQL, 16001, 20000)
    --PRINT SUBSTRING(@SQL, 20001, 24000)
    EXEC (@SQL)
END
GO

作为使用示例：

EXEC UPDATE_ALL_COLUMNS '%temp%', 'dbo', '', NULL

参数：

• @TableNameSearchFilter - 这将与 LIKE 运算符一起使用，以查找数据库中名称与该值匹配的所有表；
• @TableSchema - 表的架构（通常是 dbo）
• @TestValue - 在每个找到的表的所有列（和行）中搜索的值；
• @NewValue - 用于替换 @TestValue 的值。 也可以为 NULL。

说明：

• EXEC 语句将在数据库的“dbo”模式上查找名称包含单词“temp”的所有表，然后在所有找到的表的所有列中搜索值“”（空字符串），然后用 NULL 替换该值。
• 显然，如果您有很长的列/表名称或更新值，请确保更新参数的限制。
• 确保首先注释最后一行 (EXEC (@SQL)) 并使用 PRINT 取消注释这些行，只是为了了解该过程的作用以及最终语句的外观。
• 如果您想搜索NULL 值（即让@TestValue 为NULL），这不会工作（很可能）。 不过，也可以轻松更改它来实现此目的，方法是用 IS NULL 替换 WHERE 子句（在动态查询中）中的等号，并删除其余的当 @TestValue IS NULL 时，该行。
• 该过程会插入 NULL 值，并且只会在 NULLABLE 列中执行此操作。

希望这最终能帮助某人。