How can I update multiple columns with a Replace i

问题:

How do I update different columns and rows across a table? I want to do something similiar to replace a string in SQL server

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.

Thanks for any help or advice.

Cheers, ~ck in San Diego

回答1:

This should do the trick:

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

etc...

回答2:

For SQL Server 2005 this should do the trick:

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.

回答3:

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.

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_TEXT and NEW_TEXT accord to your requirement. Change system_type_id values if you need match not only varchar and nvarchar fields.

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.

Hope it helps.

回答4:

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.

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.