How can I update multiple columns with a Replace in SQL server?

Question

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

Answer 1

This should do the trick:

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

etc...

Answer 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.

Answer 3

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.

Answer 4

I want to use case statements in sql server 2008 using case or if else statements in the code below Any help would be greatly appreciated.

SELECT [Ip Pop].[Appl Id], [Ip Pop].[Account Number], [Ip Pop].[Rel Type Desc], [Ip Pop].[Relationship Desc]

FROM [Ip Pop]

if (Instr (Relationship Desc,'Trust', 0, 1)

||(Instr (Relationship Desc,'UTMA', 0, 1)

||(Instr (Relationship Desc,'Estate', 0, 1)

||(Instr (Relationship Desc,'Exect', 0, 1)

||(Instr (Relationship Desc,'Escrow', 0, 1)

||(Instr (Relationship Desc,'IOLTA', 0, 1)

||(Instr (Relationship Desc,'Tenant', 0, 1)) {"TUA"}

elseif|(Instr (Relationship Desc,'Joint', 0, 1)) {'JOD'}

elseif|(Instr (Relationship Desc,'Primary', 0, 1)

||(Instr (Relationship Desc,'Minor', 0, 1)

||(Instr (Relationship Desc,'Sole', 0, 1)

||(Instr (Relationship Desc,'HSI', 0, 1){"IND"}

elseif(Instr (Relationship Desc,'Corp', 0, 1)

||(Instr (Relationship Desc,'Part', 0, 1)

||(Instr (Relationship Desc,'Busi', 0, 1)

||(Instr (Relationship Desc,'Comp', 0, 1)

||(Instr (Relationship Desc,'DBA', 0, 1)

||(Instr (Relationship Desc,'Nonp', 0, 1)

||(Instr (Relationship Desc,'Limited', 0, 1)

||(Instr (Relationship Desc,'Non-P', 0, 1)

||(Instr (Relationship Desc,'Club', 0, 1){"COA"}

elseif(Instr (Relationship Desc,'Cust', 0, 1)){"CSA"}

elseif(Instr (Relationship Desc,"IRA", 0, 1)){"IRA"}

else{""}